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.