Photo by Moritz Schmidt

Using Full-Text Index For InnoDB When a Search Engine is not Feasible

The release of MySQL 5.6 came with the ability to create full-text indexes with the InnoDB Engine

Ideally, when it comes to complex queries involving text, you should want to use a search engine like ElasticSearch because they are specifically built to do what full-text index do and far more. That is not always an option available to everyone. There are also other potentially complex problems around search engines and databases that need to be addressed.

Consistency between a database and search engine are not trivial problems to solve in a scalable way.

Enter the full-text index. Historically, MySQL only included full-text indexes with their MyISAM engine which is prohibitive when you need to use a storage engine that is ACID compliant. With the release of MySQL 5.6, full-text indexes became available with the InnoDB storage engine.

Creating a Full-Text Index

First, you need to create a full-text index, and currently I have an existing table that doesn’t have one. The following is an example of creating an index named “idx_suggest” which will be used in customer suggest queries. The name of the index isn’t really important when it comes to querying.

ALTER TABLE customer
ADD FULLTEXT INDEX idx_suggest (first_name, last_name, city);

After you run the query, you will likely get a warning back.

| Level | Code | Message |
| Warning | 124 | InnoDB rebuilding table to add column FTS_DOC_ID|

If you have never added a full-text index to your table, you should see a warning return that says 124 InnoDB rebuilding table to add column FTS_DOC_ID. This is an internal id that the full-text index uses to map the words to their records.

Best of Both Worlds

The biggest problem with full-text indexes is that they don’t play nicely with regular indexes. It is really an apple and oranges kind of deal, so if you do a MATCH AGAINST query and want to filter out by any other foreign key, you are going to have a very inefficient query.

SELECT * FROM customer WHERE MATCH (first_name, last_name, city) AGAINST ("Kirk" IN NATURAL LANGUAGE MODE) AND company_id = ?;

Temporary tables to the rescue! To get the efficient matching of the fulltext query and the power of your indexes, you go through a slightly different process.

  1. Create a temporary table with the results of the fulltext query
  2. Join the results of the temporary table to the table the table with your foreign key or other index.
  3. Drop the temporary table
SELECT id, MATCH (first_name, last_name, city) AGAINST ("Kirk" IN NATURAL LANGUAGE MODE) score FROM customer WHERE MATCH (first_name, last_name, city) AGAINST ("573" IN NATURAL LANGUAGE MODE)
SELECT * FROM customer JOIN tmp_customer ON ( = WHERE company_id = 1572 AND status = 'A' ORDER BY score DESC

That is it!

This should give you far better performance than just writing one query. Make sure you do this in one transaction on the same connection since temporary tables are only visible in your current session. If you are using connection pooling (which you always should do if you can), make sure you drop the temporary table, or you will get table already exists conflicts.