Full-text search in MySQL
Normally, most of us use “SELECT * FROM table WHERE field1 LIKE ‘%$keyword%’” OR field2 LIKE ‘%$keyword%‘ ..etc” to search our table and get results. This is slow and inefficient approach, as it does return all (too many) rows in which it does find a match for “keyword” without caring for “relevancy” and time.
What is alternate to make your searches lot more fast and ensure correctness of results? Exactly! The full-text search thing!
What is Full-text search?
According to the MySQL manual, Full-text is a “natural language search”; it indexes words that appear to represent the row, using the columns you specified. As an example, if all your rows contain “MySQL” then “MySQL” won’t match much. It’s not terribly unique, and it would return too many results. However, if “MySQL” were present in only 5% of the rows, it would return those rows because it doesn’t appear too often to be known as a keyword that’s very common. (If you have “MySQL” in none of your rows, it’ll return nothing!)
What do i need to apply Full-text Search?
MySQL version 3.23.23 or better.
PHP and MYSQL knowledge (Just kidding!)
Full-text index. For example: use “ALTER TABLE table_name ADD FULLTEXT(field1, field2);” to add field1 and field2 to full-text index.
How do i write search query?
SELECT *,
MATCH(field1, field2) AGAINST ('$keyword') AS score
FROM table_name
WHERE MATCH(field1, field2) AGAINST('$keyword')
ORDER BY score DESC
In the above query score represents the relevancy score which may come like 1.2311552443038, 1.2210267538154, 0.74408202544174, …etc, enough to sort your records off!
Possibly Related posts:
- How to apply search filters in cakephp ajax pagination
Sometimes we need to filter records via custom query while using ajax pagination in a cakephp application. An easy way to achieve this is to... - Fixing ‘mysql’ is not recognized as an internal or external command in Windows7
Having WAMP installed when i try to type mysql in my windows command prompt it would give me the following error: 'mysql' is not recognized... - MYSQL custom sorting – order by field value
Yesterday i had a somewhat new (for me ) and different kind of requirement while displaying MySQL result on a page. I had a category... - A few notes on joining a MySQL table to itself to get unique combination of field data
Just a few notes on joining a MySQL table to itself to get unique combination of data stored in its fields. Suppose we have a... - Gmail search tips & tricks
Posting a small collection of Gmail search tips & tricks which i recently summed up for personal use. I hope you find it useful. Gmail...
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
