Create fulltext index on a VIEW

mysql view full-text-search multiple-tables
kylex picture kylex · Mar 15, 2011 · Viewed 8.1k times · Source

Is it possible to create a full text index on a VIEW?

If so, given two columns column1 and column2 on a VIEW, what is the SQL to get this done?

The reason I'd like to do this is I have two very large tables, where I need to do a FULLTEXT search of a single column on each table and combine the results. The results need to be ordered as a single unit.

Suggestions?

EDIT: This was my attempt at creating a UNION and ordering by each statements scoring.

(SELECT a_name AS name, MATCH(a_name) AGAINST('$keyword') as ascore 
     FROM a WHERE MATCH a_name AGAINST('$keyword'))
UNION  
(SELECT s_name AS name,MATCH(s_name) AGAINST('$keyword') as sscore 
     FROM s WHERE MATCH s_name AGAINST('$keyword'))
ORDER BY (ascore + sscore) ASC

sscore was not recognized.

Answer

Mike S picture Mike S · Mar 15, 2011

MySQL doesn't allow any form of indexes on a view, just on it's underlying tables. The reason for this is because MySQL only materializes a view when you select from it, due to the possibility of the underlying tables changing data all the time. If you had a view that returned 10 million rows, you'd have to apply a full text index to it every time you selected from it, and that takes a lot of time.

If you want full index functionality, then you might as well stick with the SQL script you've posted, and manually (or cronjob a script to) update the fulltext index of both tables on a nightly basis (or hourly if you're in that high traffic market).

Related questions

Can I create view with parameter in MySQL?

I have a view like this: CREATE VIEW MyView AS SELECT Column FROM Table WHERE Value = 2; I'd like to make it more generic, it means to change 2 into a variable. I tried this: CREATE VIEW MyView AS SELECT Column FROM …

Read More
MySQL: View with Subquery in the FROM Clause Limitation

In MySQL 5.0 why does the following error occur when trying to create a view with a subquery in the FROM clause? ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause If this is a limitation of the MySQL …

Read More
Grant a user permission to only view a MySQL view and nothing else

This question was originally using MySQL 5.1.44, but is applicable to MySQL 8.0+ too. Let's say I have a table with records inserted by different users of my application. How can I give a specific user access to only see his/her …

Read More