I’ve never been much of a fan of providing site users with a search box, there’s a thin line between valuable functionality and allowing users to avoid all meaningful content in favor of slamming your database with high traffic requests. For this and many other reasons I’ve never really explored past the surface of how to create fast, efficient and (above all) useful search queries, until recently.

My first approach when creating a search box was just to simply match a keyword against database records, ala…

select `p`.`id` from `posts` as `p` where `content` like ‘%keyword%’

This method is not very elegant really and limits the user to only one keyword which is obviously unacceptable. However, adding multiple like clauses into a query which is already searching through text fields is also not an option for a production site, especially if you want to search the title, content and author of a post all at the same time.

After a bit of poking around on everyones favorite search engine I came across the “full-text search” feature of MySQL, which promised me the perfect solution to my problem in a single line of code…

select `p`.`id` from `posts` as `p` where match( `title`, `content`, `author` ) against( ‘keyword’ )

Not only does this statement search against multiple columns but it also returns your results ordered by relevance to the original keyword. But wait, theres more! It is also possible to have MySQL return related results with the addition of a flag to the above query.

select `p`.`id` from `posts` as `p` where match( `title`, `content`, `author` ) against( ‘keyword’  with query expansion)

Meaning that if a user searches for “animal” the results may come back with “animal”, “dog”, “cat”

However, my glee was short lived, it appears that this feature is only available using the MyISAM driver (the standard driver for new tables) where as all my tables are using the InnoDB driver (for transaction support). So there lays a choice ahead, forsake the security of transactional statements in favor of a slick search facility or stay comfortably secure in my original choice of driver.

My first thought on settling the decision was to run some tests. So I set up a test database with 20 full text fields and fired 50,000 queries using the different methods.

1. MyISAM using the match function took: 2.27254986763 seconds
2. MyISAM using the like function took: 2.52992486954 seconds
3. InnoDB using the like function took: 2.45482206345 seconds

As you can see there’s not much of a difference, the testing continues…