MySQL Bugs: #45458: feature request for MySQL support of multiple clustering indexes

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:	None
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

Description:
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

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.

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.

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.

Thank you for the feature request and code contributed.

new patch for latest 5.1

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

reference: http://bugs.mysql.com/bug.php?id=51687