Best efficient way to make a fulltext search in MySQL

Question

I have 3 tables, and I want to make a query for a search term textbox. My query currently looks something like this:

SELECT Artist.* FROM Artist, Band, Instrument WHERE MATCH (Artist.name) AGAINST ('mysearchterm') OR MATCH (Band.name) AGAINST ('mysearchterm') OR MATCH (Instrument.name, Instrument.description) AGAINST ('mysearchterm');

This query is taking too much time to get executed. Is there any way to improve this? Am I doing something wrong?

Thanks

Answer 1

I would move to a fulltext search engine instead of trying to optimize this.

http://www.sphinxsearch.com/about.html

Answer 2

MYSQL has full text search support that will give much better performance.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-restrictions.html

However, I would recommend using a system designed for fulltext search if you intend to place any significant load on your application.

Answer 3

Can you do something like the following, which is not a fulltext search?

SELECT Artist.* FROM Artist, Band, Instrument WHERE Artist.name LIKE '%mysearchterm%'...

Or (my preference):

SELECT Artist.* FROM Artist, Band, Instrument WHERE Artist.name REGEXP '<regexp here>'...

Answer 4

Sorry for the late follow-up, but aren't you doing a Cartesian join against those three tables?

SELECT Artist.* FROM Artist, Band, Instrument WHERE MATCH (Artist.name) AGAINST ('mysearchterm') OR MATCH (Band.name) AGAINST ('mysearchterm') OR MATCH (Instrument.name, Instrument.description) AGAINST ('mysearchterm');

Assuming a database with 100 bands, 10 instruments, and 500 artists you're searching through 500,000 rows.

I would expect to see something like assuming you have a database where Artist belongs to one band and plays one instrument:

SELECT Artist.* FROM Artist, Band, Instrument WHERE Artist.band_id = Band.id and Artist.instrument_id = Instrument.id and (MATCH (Artist.name) AGAINST ('mysearchterm') OR MATCH (Band.name) AGAINST ('mysearchterm') OR MATCH (Instrument.name, Instrument.description) AGAINST ('mysearchterm'));