Fulltext Indexes With MySQL

A project I am involved in at the moment requires the ability to search databases for text matches in an intelligent manner from a web site, rather than using the rather brute force method of simple LIKE pattern matching, which has a few issues. One of the problems with using LIKE matching, is the rather too exact and not always intuitive way that it can return results. Another problem is that LIKE tends to become quite slow when searching across several columns of data. A useful alternative to LIKE is full text indexing, which is available on most database systems worth their salt today. A full text index based search is designed to process large amounts of data, and is happy to search several columns at once without too much trouble.

Initially the project system was to be based on a MSSQL db, the use of compound keys by the in house database systems precluded the application of full text indexes to the tables. A decision was therefore made to use a MySQL database instead, providing a lightweight and fast frontend DB for the web site. Luckily, since MySQL 3.23.23 became available, FULLTEXT indexes have been supported and have several advantages over LIKE based searches. One thing to note is that, in MySQL, FULLTEXT indexing is only available for MyISAM table types and also only on certain column types: TEXT, VARCHAR and CHAR.

Setting up Fulltext Indexes

I won’t go into setting up tables in MySQL - there’s plenty of information on how to do it on the net…! You can add FULLTEXT indexes at the time you create the table or to an already existing table. One thing to note though - if you are adding an FULLTEXT index to a new table, it is much faster to load the data first and then create the FULLTEXT index afterwards. To set up a FULLTEXT index on an already created table (in this case called ‘widgets’), just issue this on the command line:

mysql> CREATE FULLTEXT INDEX `fti_tprod_desc` ON widgets (tprod_desc);
Query OK, 22211 rows affected (0.65 sec)
Records: 22211  Duplicates: 0  Warnings: 0

Once the FULLTEXT index exists, it’s time to play!

Using Fulltext Indexes

To perform a search with the index, use the MATCH() command to name the column with the index, and AGAINST() for the text you are trying to find. For example, you have a table of widget products with a FULLTEXT indexed column of product descriptions (called tprod_desc), and you wanted to count the rows where the word ’shiny’ occurred in column tprod_desc , you could do something like this:

mysql> SELECT COUNT(*) FROM widgets WHERE MATCH(tprod_desc)
-> AGAINST('shiny');
+----------+
| COUNT(*) |
+----------+
|       16 |
+----------+
1 row in set (0.05 sec)

Searching for multiple words in the column tprod_desc is quite simple. However, multiple words are treated as though joined by OR operators; they are not searched for in phrase fashion:

mysql> SELECT COUNT(*) FROM widgets WHERE MATCH(tprod_desc)
-> AGAINST('shiny tools');
+----------+
| COUNT(*) |
+----------+
|       53 |
+----------+
1 row in set (0.05 sec)

Searching multiple columns is also pretty easy; just add more column names into the MATCH() clause like so - just be sure that each column has a FULLTEXT index!

mysql> SELECT COUNT(*) FROM widgets WHERE MATCH(tprod_desc, tprod_blurb)
-> AGAINST('shiny');
+----------+
| COUNT(*) |
+----------+
|       29 |
+----------+
1 row in set (0.07 sec)

Well, that’s about it for now - more soon about fine tuning MySQL’s full text capabilities.