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 ("searchterm") AS Relevance FROM [combination of tables goes here] WHERE MATCH (Title,Tags,Body) AGAINST ("searchterm")

Answer 1

How are you combining the tables with UNION? That's not what you want to do anyway; the objective is to combine the SELECTs.

You should probably post the query you're using.

Answer 2

simply do:

select * from table a where a.col=myval
union
select * from table b where b.col=myval
..

indices are used as they are with a normal select.

Answer 3

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)

Answer 4

With your setup being what appears to be a type of message board I assume that you have three tables (correct me if I am wrong):

1. Message Table (Message_ID, Title, Body, Description, Author)
2. Tag Table (Tag_ID, Name)
3. Message Tags (Message_ID, Tag_ID)

Here is how I would do it

SELECT Message.Message_ID, Message.Title, Message.Description, Message.Author, 
  IFNULL( 
    MATCH (Name)
    AGAINST (?)
    , 
    IFNULL(
      MATCH (Message.Title)
      AGAINST (?)
      ,
      MATCH (Message.Body)
      AGAINST (?)
    )
  ) AS Relevance 
FROM Message, Tag, Message_Tag 
WHERE Message.Message_ID = Message_Tag.Message_ID AND Message_Tag.Tag_ID = Tag.Tag_ID   
  AND (
    MATCH (Name) 
    AGAINST (?)
  OR 
    MATCH (Message.Title)
    AGAINST (?)
  OR 
    MATCH (Message.Body)
    AGAINST (?)
  )