[MDEV-6929] Port Facebook Prefix Index Queries Optimization Created: 2014-10-24  Updated: 2022-05-11  Resolved: 2014-11-03

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
Fix Version/s: 10.1.2

Type: Task Priority: Minor
Reporter: Jan Lindström (Inactive) Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 0
Labels: innodb, xtradb

Issue Links:
PartOf
is part of MDEV-6039 WebScaleSQL patches Closed
Problem/Incident
causes MDEV-12255 innodb_prefix_index_cluster_optimizat... Closed
causes MDEV-12486 Incorrect results with prefix_index_c... Closed
Relates
relates to MDEV-28540 Deprecate and ignore the parameter in... Closed
relates to MDEV-25440 Assertion `cmp_rec_rec(rec, old_rec, ... Closed

 Description   

https://github.com/facebook/mysql-5.6

commit e8f0052f9b112dc786bf9b957ed5b16a5749f7fd
commit e8f0052f9b112dc786bf9b957ed5b16a5749f7fd
commit 154c579b828a60722a7d9477fc61868c07453d08

Port v5.1 Prefix Index Queries Optimization

Summary:
Feature: Prefix Index Queries Optimization

Optimize prefix index queries to skip cluster index lookup when possible.

Currently InnoDB will always fetch the clustered index (primary key
index) for all prefix columns in an index, even when the value of a
particular record is smaller than the prefix length. This change
optimizes that case to use the record from the secondary index and avoid
the extra lookup.

Also adds two status vars that track how effective this is:

innodb_secondary_index_triggered_cluster_reads:
Times secondary index lookup triggered cluster lookup.

innodb_secondary_index_triggered_cluster_reads_avoided:
Times prefix optimization avoided triggering cluster lookup.

 Comments   
Comment by Jan Lindström (Inactive) [ 2014-11-03 ]

commit cb37c557688e2f7f0381d02f78976a1b7d1bfd65
Author: Jan Lindström <jan.lindstrom@skysql.com>
Date: Mon Nov 3 11:18:52 2014 +0200

MDEV-6929: Port Facebook Prefix Index Queries Optimization

Merge Facebook commit 154c579b828a60722a7d9477fc61868c07453d08
and e8f0052f9b112dc786bf9b957ed5b16a5749f7fd authored
by Steaphan Greene from https://github.com/facebook/mysql-5.6

Optimize prefix index queries to skip cluster index lookup when possible.

Currently InnoDB will always fetch the clustered index (primary key
index) for all prefix columns in an index, even when the value of a
particular record is smaller than the prefix length. This change
optimizes that case to use the record from the secondary index and avoid
the extra lookup.

Also adds two status vars that track how effective this is:

innodb_secondary_index_triggered_cluster_reads:
Times secondary index lookup triggered cluster lookup.

innodb_secondary_index_triggered_cluster_reads_avoided:
Times prefix optimization avoided triggering cluster lookup.

Generated at Thu Feb 08 07:15:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.