Mysql::Error: Specified key was too long; max key length is 1000 bytes

by

This is solely a MySQL issue –

MySQL has different engines – MyISAM, InnoDB, Memory…

MySQL has different limits on the amount of space you can use to define indexes on column(s) – for MyISAM it’s 1,000 bytes; it’s 767 for InnoDB. And the data type of those columns matters – for VARCHAR, it’s 3x so an index on a VARCHAR(100) will take 300 of those bytes (because 100 characters * 3 = 300).

To accommodate some indexing when you hit the ceiling value, you can define the index with regard to portions of the column data type:

CREATE INDEX example_idx ON YOUR_TABLE(your_column(50))

Assuming that your_column is VARCHAR(100), the index in the example above will only be on the first 50 characters. Searching for data beyond the 50th character will not be able to use the index.

More Related Contents:

  1. Mysql Improve Search Performance with wildcards (%%)
  2. Mysql covering vs composite vs column index
  3. Does Order of Fields of Multi-Column Index in MySQL Matter
  4. Does a UNIQUE constraint automatically create an INDEX on the field(s)?
  5. MySQL optimizing INSERT speed being slowed down because of indices
  6. MySQL – why not index every field?
  7. Which DB technology is sued to solve the following search issue? [closed]
  8. Problame with files on SQL
  9. MySQL pivot row into dynamic number of columns
  10. Best type of indexing when there is LIKE clause [duplicate]
  11. MySQL – Operand should contain 1 column(s)
  12. Restore table structure from frm and ibd files
  13. Difference between Key, Primary Key, Unique Key and Index in MySQL
  14. mysql pivot/crosstab query
  15. MySQL: Split comma separated list into multiple rows
  16. Most efficient way to get table row count
  17. UNION query with codeigniter’s active record pattern
  18. Groupwise maximum
  19. Filter by COUNT(*)?
  20. MySQL order by “best match”
  21. How to select rows that have current day’s timestamp?
  22. MySQL concatenation operator
  23. MySql – Way to update portion of a string?
  24. Using reserved words in column names
  25. MySQL INNER JOIN select only one row from second table
  26. Storing IPv6 Addresses in MySQL
  27. Insert/ Update random date in MySQL
  28. Replace null with 0 in MySQL
  29. Get total hours worked in a day mysql
  30. SQL query with avg and group by

Leave a Comment