Bug #77246 Reintroduce adaptive hash index field prefixes to speed up sequential inserts
Submitted: 4 Jun 2015 7:21 Modified: 18 Jun 2015 15:06
Reporter: Marko Mäkelä Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.2 OS:Any
Assigned to: CPU Architecture:Any

[4 Jun 2015 7:21] Marko Mäkelä 
Description:
The fix of
Bug#16852278 SIMPLIFY RECORD COMPARISONS
removed the capability of the InnoDB to build adaptive hash indexes on field prefixes (n_bytes).

Later it turned out that the adaptive hash index can speed up an insert, even when using an integer key. The test case of
Bug#75981 MySQL 5.7.5 30% slower than 5.6.23 for 1 thread write
uses a table with no secondary indexes, and a PRIMARY KEY on an integer field. The LOAD DATA INFILE is inserting the data in the order of the PRIMARY KEY. Removing the n_bytes made this benchmark perform much worse.

How to repeat:
See Bug#75981 MySQL 5.7.5 30% slower than 5.6.23 for 1 thread write

Suggested fix:
Restore the n_bytes fields that were removed in Bug#16852278 SIMPLIFY RECORD COMPARISONS
[18 Jun 2015 15:06] Daniel Price 
Fixed as of the upcoming 5.7.8, 5.8.0 releases, and here's the changelog entry:

Functionality required to build adaptive hash indexes on field prefixes
was reintroduced to improve sequential insert performance. The
functionality was removed in MySQL 5.7.2 by the fix for Bug #21198396.