Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-6067

Provide a fix for upstream bug #97001 in Percona Server

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: 5.7.27-30, 5.7.x
    • Fix Version/s: 8.0.21-12
    • Component/s: None
    • Labels:

      Description

      Bug description copied from https://bugs.mysql.com/bug.php?id=97001.

      Subject : Dangerous optimization reconsidering_access_paths_for_index_ordering

      Description:
      The reconsidering_access_paths_for_index_ordering optimization is dangerous and unwise for the general case, and there is currently no way to disable it. This optimization attempts to switch to an index that provides row ordering in preference of an index that provides efficient access in the hopes that the inefficient access will find sufficient rows to satisfy the limit quickly, but that is not guaranteed (or even necessarily common).

      This is unfortunately a common scenario in Rails applications because the implicit/default design for tables in Rails applications uses a synthetic (auto-increment) id as primary key, and pagination is often used in conjunction with these tables.

      Given a simplistic table design:

      CREATE TABLE `t` (
      `id` BIGINT NOT NULL,
      `other_id` BIGINT NOT NULL,
      `covered_column` VARCHAR(50) NOT NULL,
      `non_covered_column` VARCHAR(50) NOT NULL,
      PRIMARY KEY (`id`),
      INDEX `index_other_id_covered_column` (`other_id`, `covered_column`)
      );

      It is common to see queries that might look like:

      SELECT ... WHERE [secondary key conditions] ORDER BY `id` ASC LIMIT n

      This optimization can result in queries with low LIMITs being fast (due to using the correct index), whereas with slightly higher LIMITs they are much slower (due to performing a scan on the order-providing index).

      This particular bug/misfeature is well-represented in the bugs system already, but since there are so many reported cases, I created a new bug in order to discuss and provide a patch and solution, rather than leaving this on one of those at random. The related bugs I could find that seem to be related are:

      https://bugs.mysql.com/bug.php?id=42094
      https://bugs.mysql.com/bug.php?id=54225
      https://bugs.mysql.com/bug.php?id=57001
      https://bugs.mysql.com/bug.php?id=74030
      https://bugs.mysql.com/bug.php?id=76398
      https://bugs.mysql.com/bug.php?id=78612
      https://bugs.mysql.com/bug.php?id=78651
      https://bugs.mysql.com/bug.php?id=83298
      https://bugs.mysql.com/bug.php?id=83323
      https://bugs.mysql.com/bug.php?id=88181
      https://bugs.mysql.com/bug.php?id=92850
      https://bugs.mysql.com/bug.php?id=93845
      https://jira.percona.com/browse/PS-4935

      Thanks for your consideration.

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            alok.pathak Alok Pathak (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Time Tracking

                Estimated:
                Original Estimate - 0 minutes
                0m
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 2 hours, 30 minutes
                2h 30m

                  Smart Checklist