Fulltext Indexes were a new feature in MySQL 5.0, and they're meant to be a wonderful way of searching through large tables with Text column types (or Varchar, for that matter).

I was curious to see how a Fulltext search differed from a regular pattern match search. So I set up Fulltext Indexes on the line column (i.e. the bit with the text in). The syntax for searching through Fulltext Indexes involves the keywords MATCH and AGAINST. Let's find out how times Abraham is mentioned in the bible, first using the Fulltext Index:

And now using LIKE, i.e. regular MySQL pattern match syntax:

Hmm, that's interesting, we get more lines matching Abraham back from the pattern match (LIKE) search. How come? The next query shows all the lines which matched the LIKE search but didn't match the Fulltext search. It's a Left Join, which is the kind of query we use to find all the stuff which doesn't match the first table. Normally one would join two tables, but here we join two subqueries together, as though they were tables. It seems a little weird, but as each subquery produces a table-like result, it is legitimate SQL. We do have to provide a table alias for each subquery though, so MySQL can kid itself it's dealing with tables. The order of subqueries is important. The LIKE subquery goes first, as that is the one with the most rows in.

Bingo! They're all Abraham's rather than Abraham. MATCH only deals in whole words, whereas LIKE isn't so fussy.

This is really pointed up in the next query which shines a light on how MATCH works. It assigns a number to each row. The greater the number the better the match, or to put it another way,the higher the relevance. Notice the relevance is 0 for the lines which say Abraham's.