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. However, 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 secondary index in MySQL typically include 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.

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

The user interface for multiple clustering keys is different on MySQL and MariaDB.

Patch to MySQL

• 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