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

score 5 · Accepted Answer · answered Aug 24 '09 at 21:02

5

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

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

answered Aug 24 '09 at 21:02

Mark L

12,405
4
28
41

1

+1 for using an external engine, be it Sphinx or anything else (Lucene ? there are commercial tools too, btw) – Pascal MARTIN Aug 24 '09 at 21:05

score 4 · Answer 2 · answered Aug 24 '09 at 21:04

4

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.

answered Aug 24 '09 at 21:04

Cullen Walsh

4,318
1
17
12

Thanks for you answer, I wish I could choose more than one correct answer. – John 5 Aug 24 '09 at 21:12

score 1 · Answer 3 · answered Oct 29 '10 at 21:31

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'));

score 0 · Answer 4 · answered Aug 24 '09 at 21:07

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>'...

Best efficient way to make a fulltext search in MySQL

4 Answers4

Linked