MySQL Bugs: #45754: feature request: allow up to 32 columns per index

Bug #45754	feature request: allow up to 32 columns per index
Submitted:	25 Jun 2009 15:24	Modified:	5 Jun 2015 5:12
Reporter:	Zardosht Kasheff (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.1.35	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	columns per index, Contribution, INDEX

Description:
Currently, in MySQL, for all storage engines, only 16 columns may be part of a key. There is no reason this cannot be increased.

We propose to increase it to 32.

For storage engines that want to keep the limit of 16, they can implement max_supported_key_parts()

How to repeat:
Described above

Suggested fix:
Will contribute in a patch shortly

Thank you for the feature request.

Thank you for the feature request.

patch for bug

Attachment: 32_col_per_key.txt (text/plain), 1.14 KiB.

Added patch.

Needed to add function for MyISAM because MyISAM has its own internal macro for the maximum number of columns in an index.

The only risk with this patch would be the following line of code in table.cc, in the function create_frm:

    key_length= keys * (8 + MAX_REF_PARTS * 9 + NAME_LEN + 1) + 16;

Given that keys can have a max value of 128, we want to make sure this does not overflow. It does not. key_length will come out to 62608 when MAX_REF_PARTS is 32 and keys is 128

One problem with the patch is that it will unconditionally increase
the space needed for the keys in the .frm file and for people with a
lot of fields where the total length of the .frm is close to 64K after
the change they may not be able to ALTER the table again.

The way to fix the above would be to add the number of used key
segments to create_frm().

It should also be safe to increase the number of segments in MyISAM to 32 too.
(One will get a warning in debug log for old files, but this shouldn't be critical)

Monty was kind enough to implement this feature, with test cases, in MariaDB. Here what he stated on the internals mailing list:

I have now pushed all your proposed changes + more to the current
MariaDB 5.1 tree. (changeset 2713)

You can get them by either getting the lastest MariaDB code from
launchpad or looking at the patch
https://lists.launchpad.net/maria-developers/msg00463.html

This feature request is still flagged as "Patch pending" but it seems the patch is done some months ago now.

May someone can look at this please ?

Thanks

Will this be added to GA anytime soon?

what happened was some cluster bug got a typo in the commit message:
http://lists.mysql.com/commits/84515 that wrote 45754 instead of 47574

Adjusted status which was erroneously changed by commit with wrong bug number
in changeset description.