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

LP #1362212: Query Planner chooses wrong index for ORDER BY ... LIMIT

    Details

    • Type: Bug
    • Status: Done
    • Priority: Low
    • Resolution: Duplicate
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      **Reported in Launchpad by Markus Peter last update 06-12-2016 07:20:08

      Used Percona mysqld Ver 5.6.19-67.0 for debian-linux-gnu on x86_64 (Percona Server (GPL), Release 67.0, Revision 618)

      This is probably an upstream bug already (as it's clearly a query planner fail), but as we're using Percona I probably better report it here to avoid discussions with the MySQL maintainers on bug reports for software not "their own". Maybe you can report this upstream or so, don't know how issues like there are coordinated across the different MySQL forks.

      We have a table

      CREATE TABLE `suggestion` (
      `user_id` int(10) unsigned NOT NULL,
      `type` enum('user','group','board','newbie','dating') COLLATE latin1_german2_ci NOT NULL,
      `obj_id` int(10) unsigned NOT NULL,
      `rejected` enum('y','n') COLLATE latin1_german2_ci NOT NULL DEFAULT 'n',
      `flags` int(10) unsigned NOT NULL DEFAULT '0',
      `data` varbinary(32768) NOT NULL DEFAULT '',
      `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      PRIMARY KEY (`user_id`,`type`,`obj_id`),
      KEY `user_id` (`user_id`,`rejected`),
      KEY `type` (`type`,`created`),
      KEY `rejected` (`rejected`,`type`,`created`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

      The table is about 12 GB of data and 40 million rows.

      The query:

      SELECT obj_id, flags, data, type FROM suggestion WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC LIMIT 50;

      takes about 4 seconds on our system since a Percona 5.6 upgrade from 5.1 as the wrong index ('type') is chosen by the query planner instead of the more optimal PRIMARY KEY. Simply leaving out the "LIMIT 40" fixes the problem - then the proper index for that SELECT (the PRIMARY) will be used. We're currently working around the issue with a FORCE INDEX, but it'd be nice, if the problem could be properly fixed in the planner itself. The query planner obviously re-evaluates the chosen plan due to a low LIMIT value and then chooses a plan with a very high cost as evident from the plan trace:

      Query Plan Trace is here:

      SELECT obj_id, flags, data, type FROM suggestion WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC LIMIT 50 {
      "steps": [
      {
      "join_preparation":
      Unknown macro: { "select#"}

      },
      {
      "join_optimization": {
      "select#": 1,
      "steps": [
      {
      "condition_processing":

      Unknown macro: { "condition"}

      },

      Unknown macro: { "table_dependencies"}

      ,

      Unknown macro: { "ref_optimizer_key_uses"}

      ,
      {
      "rows_estimation": [
      {
      "table": "`suggestion`",
      "range_analysis": {
      "table_scan":

      { "rows": 40698474, "cost": 8.55e6 }

      ,
      "potential_range_indices": [

      { "index": "PRIMARY", "usable": true, "key_parts": [ "user_id", "type", "obj_id" ] }

      ,

      { "index": "user_id", "usable": true, "key_parts": [ "user_id", "rejected", "type", "obj_id" ] }

      ,

      { "index": "type", "usable": true, "key_parts": [ "type", "created", "user_id", "obj_id" ] }

      ,

      { "index": "rejected", "usable": true, "key_parts": [ "rejected", "type", "created", "user_id", "obj_id" ] }

      ],
      "setup_range_conditions": [
      ],
      "group_index_range":

      { "chosen": false, "cause": "not_group_by_or_distinct" }

      ,
      "analyzing_range_alternatives":

      Unknown macro: { "range_scan_alternatives"}

      ,
      "chosen_range_access_summary":

      Unknown macro: { "range_access_plan"}

      }
      }
      ]
      },
      {
      "considered_execution_plans": [
      {
      "plan_prefix": [
      ],
      "table": "`suggestion`",
      "best_access_path":

      Unknown macro: { "considered_access_paths"}

      ,
      "cost_for_plan": 26.922,
      "rows_for_plan": 60,
      "chosen": true
      }
      ]
      },
      {
      "attaching_conditions_to_tables": {
      "original_condition": "((`suggestion`.`flags` = 0) and (`suggestion`.`user_id` = '7723036') and (`suggestion`.`type` in ('newbie','group')) and (`suggestion`.`rejected` = 'n'))",
      "attached_conditions_computation": [
      {
      "table": "`suggestion`",
      "rechecking_index_usage": {
      "recheck_reason": "low_limit",
      "limit": 50,
      "row_estimate": 60,
      "range_analysis": {
      "table_scan":

      { "rows": 40698474, "cost": 4.88e7 }

      ,
      "potential_range_indices": [

      { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }

      ,

      { "index": "user_id", "usable": false, "cause": "not_applicable" }

      ,

      { "index": "type", "usable": true, "key_parts": [ "type", "created", "user_id", "obj_id" ] }

      ,

      { "index": "rejected", "usable": true, "key_parts": [ "rejected", "type", "created", "user_id", "obj_id" ] }

      ],
      "setup_range_conditions": [
      ],
      "group_index_range":

      { "chosen": false, "cause": "cannot_do_reverse_ordering" }

      ,
      "analyzing_range_alternatives":

      Unknown macro: { "range_scan_alternatives"}

      ,
      "chosen_range_access_summary":

      Unknown macro: { "range_access_plan"}

      }
      }
      }
      ],
      "attached_conditions_summary": [

      { "table": "`suggestion`", "attached": "((`suggestion`.`flags` = 0) and (`suggestion`.`user_id` = '7723036') and (`suggestion`.`type` in ('newbie','group')) and (`suggestion`.`rejected` = 'n'))" }

      ]
      }
      },
      {
      "clause_processing":

      Unknown macro: { "clause"}

      },

      Unknown macro: { "refine_plan"}

      ,
      {
      "reconsidering_access_paths_for_index_ordering":

      Unknown macro: { "clause"}

      }
      ]
      }
      },

      Unknown macro: { "join_execution"}

      ]
      }

      0 0

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  lpjirasync lpjirasync (Inactive)
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  2 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: