MySQL Bugs: #899: 32 keys maximum w/ a table

Bug #899	32 keys maximum w/ a table
Submitted:	23 Jul 2003 10:35	Modified:	27 Aug 2003 13:14
Reporter:	Matthias Urlichs	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	all	OS:	Any (all)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
my table creation script errors out with:

ERROR 1069 at line 34154: Too many keys specified. Max 32 keys allowed

Unfortunately, that table has more than 32 columns which refer to other columns, and I need to add FOREIGN KEY records. Since InnoDB requires indices on columns which contain foreign keys, this is a problem.

This happened on a legacy database with lots of code which requires the integrity constraints for correct operation...

How to repeat:
create table a(i int primary key);
create table b(i int primary key);
alter table b add a1 int, add index ib1(a1), add constraint cb1 foreign key(a1) references a(i);
alter table b add a2 int, add index ib2(a2), add constraint cb2 foreign key(a2) references a(i);
...
and so on. Error at #33.

Suggested fix:
Set the maximum to 64 or 128 or whatever. 32 isn't enough.

Heikki, is this something you can fix in InnoDB? Or does it need to be
fixed in the mysql code instead?

Lenz, Igor,

do not assign this feature request to me :). InnoDB supports 4 billion indexes in a table.

The restriction comes from the MySQL optimizer where a 32-bit integer is used as a 'set' type (== bitmap) to describe a set of indexes. Increasing the limit to 64 is relatively straightforward: make that integer longlong.

I have now assigned this feature request to Igor Babaev, who is responsible for the SQL optimizer.

Regards,

Heikki

A quick grep finds:

include/myisam.h:#define MI_MAX_KEY 32      /* Max allowed keys */
sql/unireg.h:#define MAX_KEY 32             /* Max used keys */

Matthias,

Thank you for your feedback,
This item is already in MySQL ToDo list and we'll fix this limitation in one of the next MySQL releases.

It will be better if the maximum number of inexes became configurable in my.ini