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.