Bug #45458 feature request for MySQL support of multiple clustering indexes
Submitted: 11 Jun 2009 17:07 Modified: 11 Jun 2009 17:23
Reporter: Zardosht Kasheff (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Storage Engines Severity:S4 (Feature request)
Version:5.1.30 OS:Any
Assigned to: CPU Architecture:Any
Tags: clustering, clustering keys, Contribution

[11 Jun 2009 17:07] Zardosht Kasheff
The TokuDB storage engine has added syntax to MySQL to allow users to declare an index as "clustering". Some examples: 

create table foo (a int, b int, clustering key (a)) engine=tokudb;
alter table foo add clustering index clstr_key(a);
create clustering index clstr_key on foo(a);

The feature request is to support this new grammar, and for all indexes that are defined as clustering, a flag is set in KEY->flags of the appropriate index.

How to repeat:
This is a feature request to allow storage engines to support multiple clustering indexes.

Suggested fix:
I will submit a patch, off of 5.1.30
[11 Jun 2009 17:10] Zardosht Kasheff
The patch adds modifies grammar to understand clustering indexes. With this patch, all indexes that are defined as clustering.

Attachment: cluster_key_patch.txt (text/plain), 5.43 KiB.

[11 Jun 2009 17:11] Zardosht Kasheff
My description of the attachment was cut off. Here it is in full:

The patch adds modifies grammar to understand clustering indexes. 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.
[11 Jun 2009 17:13] Zardosht Kasheff
Known issues with this patch:

Known issues:
MySQL bug 40358 (http://bugs.mysql.com/bug.php?id=40358) makes the saving of the HA_CLUSTERING in a table's .frm file a little hacky. As the bug mentions, the .frm file only uses 2 bytes to save a key's flags, but keys may more than 16 flags. So, as is, there is no space to store the HA_CLUSTERING flag in the .frm file. To work around this, as shown in sql/unireg.cc and sql/table.cc, we take advantage of the fact that HA_SPATIAL and HA_FULLTEXT can never both be set. To encode HA_CLUSTERING, we set both HA_SPATIAL and HA_FULLTEXT.

MySQL bug #39653 (http://bugs.mysql.com/bug.php?id=39653) is exposed even more. The bug is as follows. Currently, in MySQL, given a set of covering indexes that MySQL can use to satisfy a query, it selects the one with the shortest key length. If the key is clustering (like InnoDB's primary key), this is not a good idea, because for clustering keys, the key length is not a good indicator of how long it will take to read through the index. 

Also, regardless of whether the storage engine supports multiple clustering indexes, the command "show create table" will always show keys that the user defined as clustering to be clustering.
[11 Jun 2009 17:23] Valeriy Kravchuk
Thank you for the feature request and code contributed.
[24 Feb 2010 15:39] Zardosht Kasheff
new patch for latest 5.1

Attachment: 4-clustering_key_grammar.txt (text/plain), 6.84 KiB.

[26 Mar 2010 17:43] Jonas Oreland
reference: http://bugs.mysql.com/bug.php?id=51687