Searching multiple tables with MySQL

Question

I am writing a PHP/MySQL program and I would like to know how to search across multiple tables using MySQL.

Basically, I have a search box as in the top right of most sites, and when user's search something in that box, it needs to search in users.username, users.profile_text, uploads.title, uploads.description, sets.description and comments.text. I need to get the ID (stored in a id field in each table) and if possible, a google like excerpt.

Answer 1

You can either write your procedure to query each of these tables individually, or you could create a relatively simple view that conglomerates all of the searchable columns of the important tables along with an indicator showing which table they're from. There's not really a magic way to search multiple tables other than writing the statements normally.

The second approach would look something like this:

(SELECT 'Table 1' AS TableName, id as Id, text as Searchable
FROM table1)
UNION
(SELECT 'Table 2' AS TableName, table2_id as Id, name as Searchable
FROM table2)
UNION
...

Then search on the resulting view. It's important to note that this method won't be fast.

A similar, and faster, alternative would be to dedicate a table to this task instead of a view, and populate it on insert/update/delete of the real tables instead of recomputing it on access.

Answer 2

To write a select query that's tailored for each of the tables, you can union the results to get one resultset. It looks like it will look something like this:

 select 'user-name', id, username from users where  username like '%term%'
 union
 select 'user-profile', id, profile from users where profile like '%term%'
 union
 select 'uploads-title', id, title from uploads where title like '%term%'
 union
 select 'uploads-description', id, description from uploads where description like '%term%'