Best effective way to create full-text searches in MySQL

Question

I have 3 tables and I want to make a query for the text field of the search query. Currently my query 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 request takes too long to complete. Is there any way to improve this? Am I doing something wrong?

thanks

Answer 1

I would go to a full-text search engine, and not try to optimize it.

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

Answer 2

MYSQL supports full-text search, which 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 full-text search if you intend to place a significant load on your application.

Answer 3

Sorry for the late check, but don't you make a Cartesian join against these 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 the database contains 100 ranges, 10 instruments and 500 artists, you are browsing 500,000 rows.

I would expect to see something like that you have a database where Artist belongs to the same group 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')); 

Answer 4

Can you do something like the following, which is not a full-text 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>'...