Thursday, April 15, 2010

Implementation of Full Text Search using MySQL database


“Search? Aha…lets use wildcard for now”.

This is a very common phrase that appears when any site based on MySQL database requires “search” feature. Today, I read about another method of full text search that is not only faster but just took 10 minutes to be implemented.

Let say you are using something like:

SELECT * FROM blog WHERE body like ‘%full text query%’ OR title like ‘%full text query%’

Above query may take a few milliseconds to execute but if you are required to join this table with other tables. Huh! Hell tough. And you are just fed up by adding too many indices on the columns but  in vain. What to do? Here are some simple steps as alternative.

Step 1. Create a FULLTEXT index :
ALTER TABLE blog ADD FULLTEXT(title, body)

Step 2. Replace the above query with this one
SELECT * FROM blog WHERE MATCH(title, body) AGAINST (' full text query')

Just make sure that whatever you are passing to MATCH() is passed to FULLTEXT() in Step 1.

Step 3. There is not step 3 :P, you are done.

Advantages:
1.       Almost 10 times faster
2.       Results are sorted by relevance
3.       Each word of query string is matched.


References: