#1071 – Specified key was too long; max key length is 1000 bytes

by

As @Devart says, the total length of your index is too long.

The short answer is that you shouldn’t be indexing such long VARCHAR columns anyway, because the index will be very bulky and inefficient.

The best practice is to use prefix indexes so you’re only indexing a left substring of the data. Most of your data will be a lot shorter than 255 characters anyway.

You can declare a prefix length per column as you define the index. For example:

...
KEY `index` (`parent_menu_id`,`menu_link`(50),`plugin`(50),`alias`(50))
...

But what’s the best prefix length for a given column? Here’s a method to find out:

SELECT
 ROUND(SUM(LENGTH(`menu_link`)<10)*100/COUNT(`menu_link`),2) AS pct_length_10,
 ROUND(SUM(LENGTH(`menu_link`)<20)*100/COUNT(`menu_link`),2) AS pct_length_20,
 ROUND(SUM(LENGTH(`menu_link`)<50)*100/COUNT(`menu_link`),2) AS pct_length_50,
 ROUND(SUM(LENGTH(`menu_link`)<100)*100/COUNT(`menu_link`),2) AS pct_length_100
FROM `pds_core_menu_items`;

It tells you the proportion of rows that have no more than a given string length in the menu_link column. You might see output like this:

+---------------+---------------+---------------+----------------+
| pct_length_10 | pct_length_20 | pct_length_50 | pct_length_100 |
+---------------+---------------+---------------+----------------+
|         21.78 |         80.20 |        100.00 |         100.00 |
+---------------+---------------+---------------+----------------+

This tells you that 80% of your strings are less than 20 characters, and all of your strings are less than 50 characters. So there’s no need to index more than a prefix length of 50, and certainly no need to index the full length of 255 characters.

PS: The INT(1) and INT(32) data types indicates another misunderstanding about MySQL. The numeric argument has no effect related to storage or the range of values allowed for the column. INT is always 4 bytes, and it always allows values from -2147483648 to 2147483647. The numeric argument is about padding values during display, which has no effect unless you use the ZEROFILL option.

More Related Contents:

  1. i have an error which i dont understand. can you help me? [closed]
  2. MySQL Server has gone away when importing large sql file
  3. How to display UTF-8 characters in phpMyAdmin?
  4. Restore table structure from frm and ibd files
  5. Why is the estimated rows count very different in phpmyadmin results?
  6. Subqueries with EXISTS vs IN – MySQL
  7. phpMyAdmin on MySQL 8.0 [duplicate]
  8. Find and Replace text in the entire table using a MySQL query
  9. Setting up foreign keys in phpMyAdmin?
  10. How can I access the MySQL command line with XAMPP for Windows?
  11. How to access remote server with local phpMyAdmin client?
  12. How can I get rid of these comments in a MySQL dump?
  13. Is there a simple way to convert MySQL data into Title Case?
  14. Regaining access to lost MySQL password for PHPMyAdmin on WAMP
  15. Bug? #1146 – Table ‘xxx.xxxxx’ doesn’t exist
  16. Creating functions in phpMyAdmin – Error: access denied you need the super privilege for this operation
  17. #1273 – Unknown collation: ‘utf8mb4_unicode_ci’ cPanel
  18. Restore the mysql database from .frm files
  19. MySQL said: Documentation #1045 – Access denied for user ‘root’@’localhost’ (using password: NO)
  20. script to convert mysql dump sql file into format that can be imported into sqlite3 db
  21. Composite key as foreign key (sql)
  22. how to delete duplicate rows from a table in mysql
  23. Can’t import database through phpmyadmin file size too large
  24. #1045 – Access denied for user ‘root’@’localhost’ (using password: YES)
  25. Access denied after setting user’s password with SHA256 in phpMyAdmin
  26. SQL: deleting tables with prefix
  27. where does MySQL store database files?
  28. #1130 – Host ‘localhost’ is not allowed to connect to this MySQL server
  29. How to import large sql file in phpmyadmin
  30. Import CSV to Update rows in table

Leave a Comment