This post is for storage engine developers that may be interested in implementing multiple clustering keys.
After blogging about TokuDB’s multiple clustering indexes feature, Baron Schwartz suggested we contribute the patch to allow other storage engine to implement the feature. We filed a feature request to MySQL to support this, along with a proposed patch. The patch, along with known issues, can be found here.
This patch has the changes necessary to introduce the new grammar for clustering indexes, and to tell the storage engine what indexes are defined as clustering. With this patch, all indexes that are defined as clustering have a flag, HA_CLUSTERING, set in the KEY->flags field of the appropriate key. The primary key NEVER has this flag set.
After integrating the change, all clustering keys will have the flag HA_CLUSTERING set in the KEY->flags field. For those keys, the developer must make sure that the data is clustered. To get the query optimizer to properly use the clustering index, the developer should reimplement handler::read_time to make sure that the cost estimate for reading rows from a clustered index is accurate. A good example is InnoDB’s implementation in ha_innobase::read_time. Note that they special case the primary key (their clustered index).