We have a situation where we encounter a MySQL error

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

In our database (using Magento E-commerce) we need to add 76 indexed key columns but we are not allowed to add more than 64 keys, which is the maximum limit allowed.

So we have decided to increase the maximum limit to 80 by recompiling the MySQL / MariaDB, in that case what would be the implications ?

Kindly let me know if you need more server resource details.

Thank you !

  • 2
    Cases where such a large number of indices are needed are so rare that it can be said that they do not exist at all. Can you really prove (to yourself first) that each of these indices is critically necessary? If so, then use vertical sharding. – Akina Sep 24 '19 at 9:04
  • Yes we do need these indexes as we have an medium-large e-commerce store built using Magento platform....and we have a large number of vendors , whose category of products have different attributes which needs to be filterable...so to acheive this Magento creates a flat table where these filterable attributes are indexed. – Haijerome Sep 24 '19 at 9:26
  • 1
    Do you remember that only one index for a table copy can be used in a query, even if a lot of them are visually suitable? Additionally - in most cases there exists some field which is filtered in almost all cases with filtering by another field only, and in common there exists a lot of such pairs (or even groups contained more fields) - in such cases one composite index is suitable whereas separate indices are practically useless. – Akina Sep 24 '19 at 9:27
  • 1
    Adding to what Akina says... It may be practical to have a few, commonly, used columns indexed, then throw the rest into a JSON column for further filtering by the app. Discussion: mysql.rjweb.org/doc.php/eav – Rick James Oct 6 '19 at 1:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.