According to the answer of choice of this question: https://stackoverflow.com/questions/3489041/mysqlerror-specified-key-was-too-long-max-key-length-is-1000-bytes

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.

I don't think so. I think when query with a string longer than 50 chars, MySQL will first use the first 50 chars and index to get a match list (which generally will be a very short list), then search in that list without index.

Is it true that MySQL won't use index at all when the query string is longer than 50 chars?

  • In order to confirm or deny this statement, it is necessary to examine the source code of the relevant procedures. As an indirect fact, this index can be included in the query execution plan.
    – Akina
    Sep 24, 2019 at 4:50
  • Is it true that MySQL won't use index at all when the query string is longer than 50 chars? - do you mean on the second pass after finding this short list which matches the index in under and over 50 characters?
    – Vérace
    Sep 24, 2019 at 5:11
  • There are many cases where MySQL punts on making use of a "prefix" index. Avoid it whenever possible. It would be better to discuss your specific table and specific query.
    – Rick James
    Oct 6, 2019 at 1:16

1 Answer 1


You are only partially right. MySQL query optimization engine will still try to use that index, as long as:

  1. Query has exact match (a = 'b', a <> 'b', IN clause etc. - not "LIKE" clause),
  2. Storage engine is InnoDB, XtraDB (Percona), or possibly some other fork of InnoDB.
  3. Server is recent enough (at least 5.0 or 5.1 - I'm not sure now, but in 4.x varchar indexes worked a bit differently).
  • Tested on MySQL 5.6 Windows with a query of 560 chars, the column is varchar(5000), index length is 500. 150K records. Query exact cost 0.000 seconds, query like cost 0.047 seconds.
    – jw_
    Sep 24, 2019 at 10:57
  • Can you provide some official reference about your statements?
    – jw_
    Sep 24, 2019 at 10:58
  • If use column LIKE 'start%' that also uses the index on column Nov 24, 2021 at 1:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.