I have been given a database dump that I am trying to rebuild. When I import I get the following error:
Too many key parts specified; max 16 parts allowed
There are indeed more than 16 key parts in the dump.
I have read and understand the limitations of InnoDB, what I don’t understand is how the original database was able to create a key with more than 16 parts.
Here is the relevent part of the dump:
CREATE TABLE `v1_view` ( `view_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `view_date` date NOT NULL, `account_id` int(9) unsigned NOT NULL DEFAULT '0', .... PRIMARY KEY (`view_id`), UNIQUE KEY `viewUnique` (`view_date`,`account_id`,`player_id`,`video_id`,`order_id`,`orderlist_id`,`feed_id`,`domain_id`,`location_id`,`city_id`,`region_id`,`dma_id`,`country_id`,`continent_id`,`browser_id`,`platform_id`,`os_id`,`size_id`,`autoplay`,`volume`,`pacing`,`ad_spend`,`ad_revenue`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
A couple of thoughts I had:
- It was created with an older version of MySQL that allowed more key parts
- It is using a different
ENGINEeven though the dump says
Not sure how valid either of those are. I don’t have direct access to the original DB but can probably get more info if needed.
How is it that the original database has a unique key with so many parts? How do I replicate this database?
Somehow this was a
VIEW that turned into a
I checked; the max of 16 columns per index has existed for a ‘long time’ and applies to ‘all’ engines. Reference. I can’t explain your specific question.
Also, I am thinking there are worse problems than the failing
It is unreasonable to normalize something like “location”, while also including all the fields in this table:
That is, there should be a different table with all of those columns, but only
location_id in this table.
I recommend you manually remove the
UNIQUE line, load the data, then clean up messes such as
Four(4) years ago, someone came across the same issue but with 64 parts
- mysql error 1069 Too many keys specified; max 64 keys allowed
- See Avoiding “Too many keys specified” in MySQL
- Limit on Number of Indexes on MySQL Table
It is possible that compound indexes make this problem worse, reaching imposed limits.
The unique index you see has 23 columns. Ugh !!!
Perhaps you could make
dma_id unique worldwide. Then, ditch
country_id,continent_id from the index. Be careful with cities which sit in multiple DMAs (because of multiple ZipCodes)