MySQL Unique Key Limit

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 ENGINE even though the dump says InnoDB

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 TABLE?

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 UNIQUE.

It is unreasonable to normalize something like “location”, while also including all the fields in this table:

location_id,city_id,region_id,dma_id,country_id,continent_id

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 location_id.

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

I tried out the test script from Limit on Number of Indexes on MySQL Table on my Mac running MySQL 5.7.12. It reached the limit of 64.

It is possible that compound indexes make this problem worse, reaching imposed limits.

The unique index you see has 23 columns. Ugh !!!

What was suggested at first by RickJames is all you can do for now: Change UNIQUE KEY in the script to just KEY. You can reload from there.

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)