Performance when using truncated VARCHAR as index in MySQL

Question

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?

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).