Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6929

Port Facebook Prefix Index Queries Optimization

        Details

          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.

            Gliffy Diagrams

              Attachments

                Issue Links

                is part of

                Task - A task that needs to be done. MDEV-6039 WebScaleSQL patches

                • Major - Major loss of function.
                • Closed

                  Activity

                  Hide
                  Permalink
                  jplindst Jan Lindström added a comment -

                  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.

                  Show
                  jplindst Jan Lindström added a comment - 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.

                    People

                    • Assignee:
                      jplindst Jan Lindström
                      Reporter:
                      jplindst Jan Lindström
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      1 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: