I just read a great article on FULLTEXT searching in a MySQL database
(http://www.onlamp.com/pub/a/onlamp/2003/06/26/fulltext.html)

I took a few minutes to run some of my own test using two of the largest text fields on the notPopular.com user profile table. I wanted to check out the results of a FULLTEXT search versus a LIKE query using real data in my database.

First I did a search using the LIKE clause looking for “xdeathstarx”


SELECT *
FROM profiles
WHERE approved =1
AND
(interests_general LIKE '%xdeathstarx%'
OR
interests_music LIKE '%xdeathstarx%')

Showing rows 0 – 29 (119 total, Query took 0.0763 sec)

The query was fast, and took under a second to run

Next I created a FULLTEXT index on the “interests_general” and “interests_music” fields. It was really easy to do.


SELECT *
FROM profiles
WHERE approved =1
AND MATCH (interests_general, interests_music)
AGAINST ('xdeathstarx')

Showing rows 0 – 29 (119 total, Query took 0.0016 sec)

Talk about fast! it took 0.0016 seconds

lets compare the differences:
LIKE statements : 0.0763 sec
FULLTEXT search : 0.0016 sec

hands down the FULLTEXT search won, it was about 50x faster. If that isn’t instant results, I don’t know what are. Sure there will be more disk space used on the FULLTEXT index, but disk space is cheap, CPU cycles aren’t.

This makes me want to look more into optimizing the queries I am planning on using for the ever elusive notPop 2.0