I have a web-based program with MySQL database. I need to do a full text search on three fields.
How can I do it?
Use ‘LIKE’ is very slow, when I need combination of different situation.
So I find FULLTEXT index of MySQL.

*A full-text index in MySQL is an index of type FULLTEXT.
*Full-text indexes can be used only with MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.
*A FULLTEXT index definition can be given in the CREATE TABLE statement when a table is created, or added later using ALTER TABLE or CREATE INDEX.
*For large data sets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that, than to load data into a table that has an existing FULLTEXT index.

I add a FULLTEXT index into the current table with three fields.
The search query is simply and results is fast.
MATCH (col1,col2,…) AGAINST (expr [search_modifier])
If no results coming up, it maybe no results or the results is over 50% of the whole table.

David Yin

David is a blogger, geek, and web developer — founder of FreeInOutBoard.com. If you like his post, you can say thank you here

Leave a Reply

Your email address will not be published. Required fields are marked *