June 22, 2006
Limit on Number of Indexes on MySQL Table
YAMQ (yet another MySQL question) came up at work this 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 questions 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 (64bit) on Dual Opteron - 32 key limit
- MySQL 4.1.20 on Intel - 64 key limit
- MySQL 4.1.20 (64bit) 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/unireg.h). Changing the value requires a recompile of MySQL.
For anyone interested here's the SQL file that I run through the MySQL client.
shell> mysql -f < test_index_limit.sql ERROR 1069 (42000) at line 75: Too many keys specified; max 64 keys allowed ...
The test script creates a test_index_limit database, creates an index_limit table and attempts to put 70 indexes on that table. The script drops the database, so if you happen to have a test_index_limit database on your system please be careful.
Posted by mike at June 22, 2006 8:40 AM