What You Should Know About Limitations in MySQL Indexes

There are several limitations to how indexes are used and managed in MySQL in comparison to other RDBMS products.

Cost Based Optimizer

MySQL uses a cost based optimizer to prune the possible query tree to create the most optimal SQL execution path. MySQL has limited capabilities for using generated statistics to aid the optimizer as described. MySQL supports a small number of index hints to assist the optimizer in choosing a suitable path.

QEP Pinning

MySQL does not support the capability of pinning a specific QEP for a particular given query. There is no means of defining the QEP for a query where the data may change over time, affecting the possible QEP chosen. This results in the QEP being determined for every execution of every query.

Index Statistics

MySQL supports limited index statistics, and these vary depending on the storage engine. Using the MyISAM storage engine, the ANALYZE TABLE command generates statistics for the table. There is no way to specify a sampling amount. The InnoDB storage engine performs random sampling of data pages to generate statistics for a given table when the table is first opened, and then by a fuzzy method with a hard coded percentage change of the table rows.

The current development version of MySQL 5.6 includes the ability to save InnoDB statistics.

Function Based Indexes

MySQL does not currently support function based indexes. In addition, using functions on existing indexes results in sub-optimal performance. MySQL does support a partial column index, which is effectively a left substring. This is discussed in greater detail in Chapter 5.

You also cannot specify a reverse order of an index, as all data is effectively in ascending order. MySQL will traverse an existing index in reverse order if the DESC predicate is specified when ordering data.

Multiple Indexes per Table

As described in this chapter, by default MySQL will use only one index per table. While there are five exceptions, it is a good practice to realize this limitation in the design of your tables, indexes, and SQL statements. Further improvements in the MySQL optimizer will help improve on this limitation in the future.