MySQL Keyword Search Across Multiple Tables

Answer 1

I would use Apache Solr. Use the Data Import Handler to define an SQL query that joins all your tables together, create a fulltext index from the result of joined data.

---

The columns named as args to MATCH() must be the column(s) you defined for the index, in the same order you defined in the index. But you can't define any index (fulltext or otherwise) across multiple tables in MySQL.

So you can't do this:

WHERE MATCH (g.title, a.title, a.artist, t.title) AGAINST ('beatles')

It doesn't matter whether you're using boolean mode or natural language mode.

You need to do this:

WHERE MATCH (g.title) AGAINST ('beatles')
   OR MATCH (a.title, a.artist) AGAINST ('beatles')
   OR MATCH (t.title) AGAINST ('beatles')

---

You may also be interested in my presentation Practical Full-Text Search in MySQL.

Answer 2

Define a fulltext index on the four columns you like to search and then do:

SELECT * FROM genre AS g
  LEFT JOIN album AS a ON g.id = a.genre_id
  LEFT JOIN tracks AS t ON a.id = t.album_id
  WHERE MATCH (g.title,  a.title, a.artist, t.title) AGAINST ('searchstring');

The resullt will be sorted by relevancy. See here for more details on fulltext search: http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

Answer 3

I would use something like Sphinx, u can make an index out of your query and then query that. It's a little difficult to get your head around but the results are 10 times better than mysql AGAINST and you won't have problems later on with speed.