Bug #81181 Unclear how queries work when a search term exceeds an index prefix
Submitted: 22 Apr 2016 16:16 Modified: 4 Mar 2019 15:52
Reporter: Eric Lewis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[22 Apr 2016 16:16] Eric Lewis
Description:
It is unclear in the Reference Manual how queries work when a search term exceeds an index prefix.

e.g. if a column has an index with a prefix length of 767 bytes/191 characters, what happens if a search term includes a 192 character string for that column?

As I've been told in the IRC support room and reading in Stack Overflow (http://stackoverflow.com/a/31529672/1104036) the query would use the index where it is useful to rule out rows that are definitely not a match, and then touch rows in the table that are possible matches. 

I think we could describe this behavior in the Reference Manual, as it is important to understand. 

How to repeat:
Look at the Reference Manual.

Suggested fix:
Perhaps describe this behavior here? on the How MySQL Uses Indexes page (http://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html)?
[25 Apr 2016 14:29] MySQL Verification Team
In principle, it is quite simple. Index will be used for all searches where the constant string is smaller or equal to the index prefix.

If this is not well explained , then it should be ......
[4 Mar 2019 15:52] Daniel Price
Posted by developer:
 
The following section was updated:

https://dev.mysql.com/doc/refman/5.5/en/column-indexes.html#column-indexes-prefix

"If a search term exceeds the index prefix length, the index is used to exclude 
non-matching rows, and the remaining rows are examined for possible matches."