MySQL full text search across multiple tables

Question

I have a series of tables that contain data I want to full text search. I've tried combining the tables with UNION, but the result loses its fulltext index so can't be fulltext searched. I don't think that putting the data into a temp table is the way to go. Is there someway that I can fulltext search these tables efficiently? Thanks in advance!

UPDATE: my query for fulltext was

SELECT ID, Title, Description, Author, MATCH (Title,Tags,Body) AGAINST ("search terms") AS Relevance 
FROM [combination of tables goes here] 
WHERE MATCH (Title,Tags,Body) AGAINST ("search terms")

Answer 1

MySQL can't make a fulltext (or any) index accross multiple tables. So using a single index is out.

As an alternative, you could either:

1. Use an index on each table, and a join/union as appropriate to retrieve the rows that match your requirements.

2. Create an aggregate table to apply the index to.

3. Use a tool such as lucene or solr to provide your search index. (If you are going for any sort of scale, this is likely the best option)