Fred

Most people* immediately think of the ‘LIKE’ operator when they want to do a quick search in SQL. If you only want to search for a single keyword in a small table, then there is (arguably) nothing wrong with this.

However, when you start wanting to search for multiple terms, search for certain terms while excluding others, or searching large tables, ‘LIKE’ will get slow and very inaccurate.

One major cause of this inaccuracy is that the search terms are looked for in the order they are specified. For example, the following query would match “We use PHP and MySQL to power this website.”, but would not match “We use MySQL and PHP to power this website.”:

SELECT * FROM articles WHERE body LIKE '%php%mysql%' AND body NOT LIKE '%wordpress%';

After discovering the above limitation first-hand (while implementing a search box on my QuoteDB system), I came an alternative: Full-Text matching.

This article on MySQL Full-Text Searching on the Zend DevZone was particularly helpful on this topic, so I recommend you give it a read. For those too lazy to read the entire thing, Full-Text matching requires a single step before you can use it: you must first add a  ‘FULLTEXT’ index to the field(s) you wish search:

ALTER TABLE articles ADD FULLTEXT(body);

Once that’s done, you can perform very fast and accurate searches, including (since MySQL 4.0.1) boolean mode operators to dramatically increase the search power. (For an idea of the power of boolean mode, have a look at the operators table.)

SELECT *, MATCH(body) AGAINST ('+php +mysql -wordpress' IN BOOLEAN MODE) AS score FROM articles
WHERE MATCH(body) AGAINST ('+php +mysql -wordpress' IN BOOLEAN MODE);

Unlike the ‘LIKE’ search, thanks to Full-Text matching, this query would match both of the “We use…” text strings.

* This included myself, until I discovered Full-Text matching.