9.3.18     Advanced text searching using full-text indexes

This is NOT the latest copy of this book; click here for the latest version.

Creating an index of large text fields is not practical in its default state - text fields have certain characteristics that warrant their own form of indexing. For example, many simple words ("at", "the", etc) are unimportant, and therefore aren't stored in FULLTEXT indexes. Furthermore, MySQL has an infamous "50% rule", whereby it will not bother indexing words that appear in the more than 50% of records simply because it will not be of use.

Creating a full text index is done after creating your table, using the following command: ALTER TABLE some_table ADD FULLTEXT (some_field). Here is an example:

Once we have a FULLTEXT index on our table, it opens up a whole new world of pattern matching, which, as of MySQL 4, allows us to do boolean matching with little fuss. FULLTEXT queries look a little more complicated that normal SELECT queries, but are much more powerful - they can even be used to return "match quality", as you will soon see. To properly demonstrate FULLTEXT indexes, we need a table with lots of text in:

That gives us a very basic table with a FULLTEXT index on the opinion text. From there, we can jump in with a very basic SELECT query to match all rows with "nice" in:

That query generates the following output:

As you can see, the query has indeed matched the three rows that contain the string "nice". One important thing to note is that the minimum size of word that MySQL will index is, by default, four characters - anything smaller than that is rarely worthwhile.

Now consider this second query:

What do you think it will output? If you thought it would output just one row, you would be wrong - MySQL uses OR by default when matching words, meaning it will return any row that matches "nice" or matches "language" - this is where boolean mode searches come in.

Boolean mode searching has long been popular with Internet search engines - they allow you to proceed words with a + or a - to force it to either be present (+) or not present (-). You can switch to boolean mode searching using FULLTEXT indexes by adding "IN BOOLEAN MODE" to the query, like this:

Note that the "IN BOOLEAN MODE" modifier comes inside the AGAINST brackets. This time the query returns just one result - "Spain is a nice country to visit". This is because "-language" means that MySQL will not return any rows that match "language", even if they match "nice". Searching in MySQL has a number of other ways it can be used - for example, putting double quotes around groups of words allow phrase searching. That is, to match precisely "nice language", we would use the following query:

That matches rows that have "nice language" just like that - no words in between, not one or the other. As such, only "PHP is a very nice language" is matched.

There are other boolean operators, although they get more complicated. For example, > and < mark that the following word has a higher or lower relevance respectively than other words. The tilde symbol, ~, means that the following word should contribute negatively to the relevance of the row - this is not the same as <, which marks a word as being less relevant but still relevant nonetheless, and -, which excludes a word altogether. The asterisk symbol, *, allows wildcard matching. Finally, you can use brackets, ( and ), to group words into subexpressions.

So, all of these examples are possible boolean queries:

Before we take a look at how these effect queries in practical use, I'd first like to introduce the topic of relevance. Result relevant, often known as match quality or score, is a number returned by MySQL that allows you to rank how much of a query a row matched. To get relevance from a query, simply copy the full-text WHERE clause into the fields you want to select, like this:

Do not worry about using MATCH() twice - the two matches are identical, and the MySQL optimizer will pick up on this and only execute it once. However, it does give us a new field, Score, to play with. So, the query above says "Select all opinions and their relevance where the row has either "nice" or "language". Here is what the above query outputs:

Naturally it would be best to sort by Score descending to get the matches in order of most relevant first, however note that the Score of the two fields with "nice" and language in is 2, whereas the field with just "nice" in is 1. Now we get to play with the extended boolean mode operators - for example, if we wanted to match "nice language" or "nice country", we could use this query:

The pluses are in there to make sure that both nice and either language or country are included in the result. This time Score is 2 for all three fields, as all three match nice and all three have either language or country. We can give more relevancy information to MySQL by saying that country is more important to match than language by using >, like this:

This time we get 1 for the two "nice language" matches and 1.25 for "nice country" - MySQL has increased the score for "country" by 0.25. We can push this further by decreasing the score for "language" at the same time, like this:

The output for that query is shown below - as you can see, the nice language matches have moved down another notch.

The exact numbers in the score are usually irrelevant - it is the ranking that counts. Therefore, we could have achieved the same effect by using the negation operator, ~, which would subtract points from the word language if we used this query:

This time MySQL will match all rows that contain nice or language, but will "penalise" rows that contain language. This is helpful for marking known irrelevant words without specifically removing rows that contain them.