Using replication to provide full-text indexing for InnoDB tables

The InnoDB storage engine is the one most commonly used nowadays because it provides more enterprise-level features than MyISAM and most other engines. However, InnoDB tables do have a major drawback: they do not support full-text indexing. This can be a significant obstacle when you have to design any sort of application that relies on atomic operations and must store text data in a searchable manner.

While there are third-party products available to redress this shortcoming, there are times you may need to refrain from using these and stick to the out-of-the-box functionality. If you are willing to provide an additional server and make slight adjustments to your application code, replication can help you provide a full-text index for InnoDB tables indirectly.

This recipe is similar to the one about Sharing read load across multiple machines in this chapter. In contrast, only queries that are targeted at the full-text index need to be sent to a slave machine. This will require slight changes to the application code.

Getting ready

To follow along with this recipe, you will need two MySQL servers available—a master and a slave. For testing, these might reside on the same physical machine. In a production environment we do, however, recommend two separate pieces of equipment.

They will be referred to as master and slave in this example. Substitute your concrete host names appropriately.

You will need privileges to change the application source code. This is usually a task that requires cooperation with the application developers.

How to do it...

1. On the master, identify the table that contains the column(s) that you want to equip with a full-text index. In this example, we use the following table definition from a fictional forum application:

   CREATE TABLE `posts` (
   `id` int(11) NOT NULL auto_increment,
   `title` varchar(100) NOT NULL,
   `posttext` text NOT NULL,
   PRIMARY KEY (`id`)
   ) ENGINE=InnoDB;
   

   The posttext column contains the text of forum posts. As the table is created with ENGINE=InnoDB, we cannot add a full-text index to it.

2. On the slave, create the same table, but with a slightly modified definition:

   CREATE TABLE `posts` (
   `id` int(11) NOT NULL auto_increment,
   `title` varchar(100) NOT NULL,
   `posttext` text NOT NULL,
   PRIMARY KEY (`id`),
   FULLTEXT KEY `FT_text` (`posttext`)
   ) ENGINE=MyISAM;
   

   The storage engine is set to MyISAM, allowing the FULLTEXT KEY `FT_text` (`posttext`) definition. Trying to add this on the master would result in an error message.

3. Make sure the replication rules between master and slave include the posts table.
4. Modify your application to access the slave when doing full-text queries. It is generally considered a good practice to concentrate all database access to a dedicated module or class, so that you can easily modify your application's interaction with the underlying data store.

How it works...

In this replication setup, whenever you make changes to the master's posts table, those will be replicated to the slave, but the target table uses a different storage engine than the master. As SBR simply sends over SQL statements without any information about the origin, the slave will execute the instructions blindly. While this can be a problem in other circumstances because it makes the whole process somewhat fragile, it plays to our advantage in this case.

Upon UPDATE or INSERT to the posttext column the MyISAM engine will update the full-text index appropriately. This enables the application to issue queries using the full-text query syntax against the slave.

Note

An important drawback you must take into account is that you cannot JOIN tables between different MySQL servers!

A workaround is required when you have to, for example, join the posts with a user accounts table via the posts.id column. To implement this you will need to issue two separate queries. The first one using the full-text search on the slave will bring up all posts containing the search terms. From the resulting rows you can then take the id column values and run a second query against the master database, substituting the text search with an id lookup.

There's more...

MyISAM's full-text index has existed for several years, but has not been improved a great deal over time. If you have many concurrent requests you will notice significant resource usage, limiting scalability.

Over the past few years, several third-party vendors have stepped up with alternative solutions to the problem of full-text search, offering more features and better performance.

One of those products, offering tight integration with MySQL and PHP, is Sphinx—an open-source product available for free from http://www.sphinxsearch.com. If you find that MySQL's built-in capabilities are either too slow or too limited in other respects to meet your application's requirements, you should definitely have a look at it.

Setting up new slaves in this scenario

You should not simply use a regular SQL dump to initialize the slave, as it will contain a create table statement that specifies InnoDB and does not include the full-text index. Of course, you could change the table type after the import is complete. However, this can be time consuming. Instead, we recommend you first create the target schema on the slave, making sure the tables in question are created with ENGINE=MyISAM.

Then go ahead and import the data into the table. Only after that, add the full-text index. This is typically much faster than having the index in place beforehand because MySQL must then update it all the way through the bulk insert of rows. This is a very expensive operation compared to the delayed index creation.

See also

• Adding a full-text index in Chapter 2,Indexing.