0

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 '19 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 '19 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 '19 at 1:16
0

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).
| improve this answer | |
  • 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 '19 at 10:57
  • Can you provide some official reference about your statements? – jw_ Sep 24 '19 at 10:58

Your Answer

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

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