ERROR 1069: Too many keys specified. Max XX keys allowed

FROM: Limit on Number of Indexes on MySQL Table

YAMQ(yet another MySQL question) came up at work with past week. Some of our old data warehousing libraries work under the assumption that MySQL can only handle 16 indexes (built during the early 3.23.x days). So the question is how many indexes can a single table have these days?

This is similar to last week's question on how many joins MySQL can handle. I don't see much in the way of official documentation, but dug around the forums and found good information in this thread.

Creating an index requires creating a key, and there's a limit placed on the number of keys allowed for a table. Thus the limit on indexes is governed by the number of keys you are allowed to create. The error message when you've created one too many keys looks like:

ERROR 1069: Too many keys specified. Max XX keys allowed

And in the documentation:

Error: 1069 SQLSTATE: 42000 (ER_TOO_MANY_KEYS)
Message: Too many keys specified; max %d keys allowed

A quick run through a few machines/architectures I have access to gives a hint at the limit on various systems:

• MySQL 3.23.58 on AMD Athlon XP - 32 key limit


• MySQL 4.0.23 on Intel - 32 key limit


• MySQL 4.0.23(64 bit) on Dual Opteron - 32 key limit


• MySQL 4.1.20 on Intel - 64 key limit


• MySQL 4.1.20(64 bit) on Dual Opteron - 64 key limit


• MySQL 5.0.20 on Dual-Core Intel Mac - 64 key limit

From the forum post it appears that this limit is controlled by the MAX_KEY variable in the source code(sql/unireq.h). Changing the value requires a recompile of MySQL.

For anyone interested here's the SQL file that I ru through the MySQL client.
[bash]
# mysql -f < test_index_limit.sql
ERROR 1069 (42000) at line 75: Too many keys specified; max 64 keys allowed
...
[/bash]

The test script creates a test_index_limit database, create an index_limit table and attempt to put 70 indexes on that table. The script drop the database, so if you happen to have a test_index_limit database on your system please be careful.

目前，普通的服务器，根据其硬件及架构，基本上每个表最多支持 64 个 索引。

附：
[sql title="test index limit sql"]
create database test_index_limit;

use test_index_limit;

create table index_limit (customer_id_1 int, customer_id_2 int, customer_id_3 int, customer_id_4 int, customer_id_5 int, customer_id_6 int, customer_id_7 int, customer_id_8 int, customer_id_9 int, customer_id_10 int, customer_id_11 int, customer_id_12 int, customer_id_13 int, customer_id_14 int, customer_id_15 int, customer_id_16 int, customer_id_17 int, customer_id_18 int, customer_id_19 int, customer_id_20 int, customer_id_21 int, customer_id_22 int, customer_id_23 int, customer_id_24 int, customer_id_25 int, customer_id_26 int, customer_id_27 int, customer_id_28 int, customer_id_29 int, customer_id_30 int, customer_id_31 int, customer_id_32 int, customer_id_33 int, customer_id_34 int, customer_id_35 int, customer_id_36 int, customer_id_37 int, customer_id_38 int, customer_id_39 int, customer_id_40 int, customer_id_41 int, customer_id_42 int, customer_id_43 int, customer_id_44 int, customer_id_45 int, customer_id_46 int, customer_id_47 int, customer_id_48 int, customer_id_49 int, customer_id_50 int, customer_id_51 int, customer_id_52 int, customer_id_53 int, customer_id_54 int, customer_id_55 int, customer_id_56 int, customer_id_57 int, customer_id_58 int, customer_id_59 int, customer_id_60 int, customer_id_61 int, customer_id_62 int, customer_id_63 int, customer_id_64 int, customer_id_65 int, customer_id_66 int, customer_id_67 int, customer_id_68 int, customer_id_69 int, customer_id_70 int);

alter table index_limit add index (customer_id_1);
alter table index_limit add index (customer_id_2);
alter table index_limit add index (customer_id_3);
alter table index_limit add index (customer_id_4);
alter table index_limit add index (customer_id_5);
alter table index_limit add index (customer_id_6);
alter table index_limit add index (customer_id_7);
alter table index_limit add index (customer_id_8);
alter table index_limit add index (customer_id_9);
alter table index_limit add index (customer_id_10);
alter table index_limit add index (customer_id_11);
alter table index_limit add index (customer_id_12);
alter table index_limit add index (customer_id_13);
alter table index_limit add index (customer_id_14);
alter table index_limit add index (customer_id_15);
alter table index_limit add index (customer_id_16);
alter table index_limit add index (customer_id_17);
alter table index_limit add index (customer_id_18);
alter table index_limit add index (customer_id_19);
alter table index_limit add index (customer_id_20);
alter table index_limit add index (customer_id_21);
alter table index_limit add index (customer_id_22);
alter table index_limit add index (customer_id_23);
alter table index_limit add index (customer_id_24);
alter table index_limit add index (customer_id_25);
alter table index_limit add index (customer_id_26);
alter table index_limit add index (customer_id_27);
alter table index_limit add index (customer_id_28);
alter table index_limit add index (customer_id_29);
alter table index_limit add index (customer_id_30);
alter table index_limit add index (customer_id_31);
alter table index_limit add index (customer_id_32);
alter table index_limit add index (customer_id_33);
alter table index_limit add index (customer_id_34);
alter table index_limit add index (customer_id_35);
alter table index_limit add index (customer_id_36);
alter table index_limit add index (customer_id_37);
alter table index_limit add index (customer_id_38);
alter table index_limit add index (customer_id_39);
alter table index_limit add index (customer_id_40);
alter table index_limit add index (customer_id_41);
alter table index_limit add index (customer_id_42);
alter table index_limit add index (customer_id_43);
alter table index_limit add index (customer_id_44);
alter table index_limit add index (customer_id_45);
alter table index_limit add index (customer_id_46);
alter table index_limit add index (customer_id_47);
alter table index_limit add index (customer_id_48);
alter table index_limit add index (customer_id_49);
alter table index_limit add index (customer_id_50);
alter table index_limit add index (customer_id_51);
alter table index_limit add index (customer_id_52);
alter table index_limit add index (customer_id_53);
alter table index_limit add index (customer_id_54);
alter table index_limit add index (customer_id_55);
alter table index_limit add index (customer_id_56);
alter table index_limit add index (customer_id_57);
alter table index_limit add index (customer_id_58);
alter table index_limit add index (customer_id_59);
alter table index_limit add index (customer_id_60);
alter table index_limit add index (customer_id_61);
alter table index_limit add index (customer_id_62);
alter table index_limit add index (customer_id_63);
alter table index_limit add index (customer_id_64);
alter table index_limit add index (customer_id_65);
alter table index_limit add index (customer_id_66);
alter table index_limit add index (customer_id_67);
alter table index_limit add index (customer_id_68);
alter table index_limit add index (customer_id_69);
alter table index_limit add index (customer_id_70);

drop database test_index_limit;
[/sql]