Multiple Clustering Indexes

Feature Summary

Covering indexes can result in orders of magnitude performance improvements for queries. Bradley's presentation on covering indexes describes what a covering key is, how it can effect performance, and why it works. A secondary index in MySQL typically includes the key columns and the primary key columns. A query that uses a secondary index of this type is a covering query if all of the columns needed by the query are in the secondary or primary key. Otherwise, it is an uncovered query. The performance of covered queries is much better than uncovered queries because covered queries never need to do hidden lookups on the primary index to get the uncovered columns.

The definition of covering indexes can get cumbersome since MySQL limits the number of columns in a key to 16 (32 on MariaDB). Tokutek introduced multiple clustering indexes into MySQL to address these problems. A clustering index is an index where all of the columns for a row are located close to the key for the row. So, when one retrieves the key, one also gets the other columns. Hidden lookups on the primary index are not necessary since all of the columns are clustered with the key.

Zardosht describes the multiple clustering indexes feature and how clustering indexes differ from covering indexes. Zardosht also describes the query versus update tradeoffs that exist with clustering indexes.

User interface for multiple clustering indexes

Patch for Multiple Clustering Indexes in MySQL

Blogs

• http://www.tokutek.com/2009/05/clustering_indexes_vs_covering_indexes
• http://www.tokutek.com/2009/05/covering_indexes_orders_of_magnitude_improvements
• http://www.tokutek.com/2009/05/introducing_multiple_clustering_indexes
• http://www.tokutek.com/2009/06/how_clustering_indexes_sometimes_help_update_and_delete_performance
• http://www.tokutek.com/2009/06/mysql_51_grammar_changes_to_support_clustering_indexes

Implementation