Search in multiple tables

Question

Let's say I have tree tables:

[ news ]
    id
    title
    text
    date

[ posts ]
    id
    title
    text
    date

[ gallery ]
    id
    title
    text
    date

How can I perform a FULLTEXT search in these tree tables with one query?

I just want to run the search on the title and text fields and get the id, title, date and the table name with the results... Is this possible?

» Update - Most important: I need order the result by the FULLTEXT score DESC (possible mixing the different table results)

Answer 1

If there is a sensible relationship between the tables, join them on that relationship.

If these are really the same data with different identities, maybe they should be re-factored into a single table with a type field.

Answer 2

You can make this in one query using UNION ALL, but you'll have to use three parameters -- all three the same search value -- and it's going to have the same database load, if not more, than doing three separate queries, as all of the results will be pulled into a temporary table to sort them.

Try something like:

SELECT id, title, text, date, relevance
  FROM (
  SELECT id, title, text, date, MATCH(text) AGAINST (?) AS relevance
    FROM news
  UNION ALL
  SELECT id, title, text, date, MATCH(text) AGAINST (?) AS relevance
    FROM posts
  UNION ALL
  SELECT id, title, text, date, MATCH(text) AGAINST (?) AS relevance
    FROM gallery
  ) temp_sort_table
  ORDER BY relevance DESC