Create fulltext index on a VIEW

Question

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.

Tetravalent

Answer 1

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).

Thunderhead

Answer 2

A kindof ugly workaround that might be inefficient but gets the job done would be to join the view with the underlying table to access the fulltext columns:

/* join the view with the underlying table, that is used in the view: */
select item.* from items_view as item
left join items as raw_item on raw_item.id = item.id
/* use the `item` from the view as usual: */
where item.foo = 'bar'
/* but use `raw_item` when you need the fulltext index: */
where match (raw_item.content) against ('foo bar')

Of course, this isn't a relevant solution if you need to create a new column in the view and give it the fulltext index, but if you only need to access the original data in this way and you just need the view for other unrelated operations, then this might be the way to go.

Rajasthani