After the MySQL ORDER BY primary key id plus LIMIT limit reaches a certain threshold, why not expect the index and go to the primary key index?

background and phenomena

  • the amount of data in the report_product_sales_ data table is 28 million;
  • it has been tested that when the, order by primary key id,limit reaches 49 under the current data volume, the PRIMARY primary key index can be used when the index report_product_sales_data_hq_code_orgz_id_index, is greater than 49.

Table structure 

CREATE TABLE `report_product_sales_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT "ID",
  `hq_code` char(16) COLLATE utf8_unicode_ci NOT NULL COMMENT "",
  `product_id` int(10) unsigned NOT NULL COMMENT "ID",
  `orgz_id` int(10) unsigned NOT NULL COMMENT "ID",
  `sales_num` double(16,3) NOT NULL COMMENT "",
  `report_date` date NOT NULL COMMENT "",
  `status` tinyint(4) NOT NULL DEFAULT "0" COMMENT ": 0.1.",
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `report_product_sales_data_unique` (`hq_code`,`report_date`,`orgz_id`,`product_id`),
  KEY `report_product_sales_data_hq_code_orgz_id_index` (`hq_code`,`orgz_id`,`report_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT="";

Explain command to view the execution plan 

-- trace
{
  "steps": [
    {
      "join_preparation": {
        "select-sharp": 1,
        "steps": [
          {
            "expanded_query": "/* select-sharp1 */ select `report_product_sales_data`.`product_id` AS `product_id`,`report_product_sales_data`.`sales_num` AS `sales_num`,`report_product_sales_data`.`report_date` AS `report_date` from `report_product_sales_data` where ((`report_product_sales_data`.`hq_code` = "000030") and (`report_product_sales_data`.`orgz_id` = 229) and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > "2018-05-11")) order by `report_product_sales_data`.`id` desc limit 10"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select-sharp": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`report_product_sales_data`.`hq_code` = "000030") and (`report_product_sales_data`.`orgz_id` = 229) and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > "2018-05-11"))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`report_product_sales_data`.`hq_code` = "000030") and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > "2018-05-11") and multiple equal(229, `report_product_sales_data`.`orgz_id`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`report_product_sales_data`.`hq_code` = "000030") and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > "2018-05-11") and multiple equal(229, `report_product_sales_data`.`orgz_id`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`report_product_sales_data`.`hq_code` = "000030") and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > "2018-05-11") and multiple equal(229, `report_product_sales_data`.`orgz_id`))"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`report_product_sales_data`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`report_product_sales_data`",
                "field": "hq_code",
                "equals": ""000030"",
                "null_rejecting": false
              },
              {
                "table": "`report_product_sales_data`",
                "field": "hq_code",
                "equals": ""000030"",
                "null_rejecting": false
              },
              {
                "table": "`report_product_sales_data`",
                "field": "orgz_id",
                "equals": "229",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`report_product_sales_data`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 28276082,
                    "cost": 6.14e6
                  },
                  "potential_range_indices": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "report_product_sales_data_unique",
                      "usable": true,
                      "key_parts": [
                        "hq_code",
                        "report_date",
                        "orgz_id",
                        "product_id"
                      ]
                    },
                    {
                      "index": "report_product_sales_data_hq_code_orgz_id_index",
                      "usable": true,
                      "key_parts": [
                        "hq_code",
                        "orgz_id",
                        "report_date",
                        "id"
                      ]
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "report_product_sales_data_unique",
                        "ranges": [
                          "000030 <= hq_code <= 000030 AND 2018-05-11 < report_date"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 1848962,
                        "cost": 2.22e6,
                        "chosen": true
                      },
                      {
                        "index": "report_product_sales_data_hq_code_orgz_id_index",
                        "ranges": [
                          "000030 <= hq_code <= 000030 AND 229 <= orgz_id <= 229 AND 2018-05-11 < report_date"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 37088,
                        "cost": 44507,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "report_product_sales_data_hq_code_orgz_id_index",
                      "rows": 37088,
                      "ranges": [
                        "000030 <= hq_code <= 000030 AND 229 <= orgz_id <= 229 AND 2018-05-11 < report_date"
                      ]
                    },
                    "rows_for_plan": 37088,
                    "cost_for_plan": 44507,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`report_product_sales_data`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "report_product_sales_data_unique",
                      "rows": 1.85e6,
                      "cost": 1.82e6,
                      "chosen": true
                    },
                    {
                    //report_product_sales_data_hq_code_orgz_id_indexcost
                      "access_type": "ref",
                      "index": "report_product_sales_data_hq_code_orgz_id_index",
                      "rows": 37088,
                      "cost": 44506,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "rows": 27816,
                      "cost": 51924,
                      "chosen": false
                    }
                  ]
                },
                "cost_for_plan": 44506,
                "rows_for_plan": 37088,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`report_product_sales_data`.`orgz_id` = 229) and (`report_product_sales_data`.`hq_code` = "000030") and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > "2018-05-11"))",
              "attached_conditions_computation": [
                {
                  "access_type_changed": {
                    "table": "`report_product_sales_data`",
                    "index": "report_product_sales_data_hq_code_orgz_id_index",
                    "old_type": "ref",
                    "new_type": "range",
                    "cause": "uses_more_keyparts"
                  }
                }
              ],
              "attached_conditions_summary": [
                {
                  "table": "`report_product_sales_data`",
                  "attached": "((`report_product_sales_data`.`orgz_id` = 229) and (`report_product_sales_data`.`hq_code` = "000030") and (`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938)) and (`report_product_sales_data`.`report_date` > "2018-05-11"))"
                }
              ]
            }
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`report_product_sales_data`.`id` desc",
              "items": [
                {
                  "item": "`report_product_sales_data`.`id`"
                }
              ],
              "resulting_clause_is_simple": true,
              "resulting_clause": "`report_product_sales_data`.`id` desc"
            }
          },
          {
            "refine_plan": [
              {
                "table": "`report_product_sales_data`",
                "pushed_index_condition": "((`report_product_sales_data`.`orgz_id` = 229) and (`report_product_sales_data`.`hq_code` = "000030") and (`report_product_sales_data`.`report_date` > "2018-05-11"))",
                "table_condition_attached": "(`report_product_sales_data`.`product_id` in (11453,11472,11487,11446,11456,12088,11433,114170,11479,11491,11485,11482,70672,68998,154298,11435,11481,11515,122573,167938))",
                "access_type": "range"
              }
            ]
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
            //order by idMySQLPRIMARY
              "clause": "ORDER BY",
              "index_order_summary": {
                "table": "`report_product_sales_data`",
                "index_provides_order": true,
                "order_direction": "desc",
                "disabled_pushed_condition_on_old_index": true,
                "index": "PRIMARY",
                "plan_changed": true,
                "access_type": "index_scan"
              }
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select-sharp": 1,
        "steps": [
        ]
      }
    }
  ]
}

question

through the phenomenon, we can see that when MySQL order by primary key id, the threshold value reaches a certain critical value (in the data environment I tested, limit > = 50 will take the primary key index), the execution plan is changed and the primary key index is selected, but I don"t know what the specific rules are. With the guidance of the Great God, why does this change of implementation plan occur?

Mysql mysql-optimizes mysql-index
Mar.16,2021

Optimizer is based on the comprehensive consideration of RBO and CBO, not necessarily the most efficient index., full scan table and full scan index are sometimes more efficient.

suppose you query LIMIT 1, and the data that meets the WHERE condition has just been inserted. Is the WHERE conditional index faster, or is the ORDER BY id primary key faster? Obviously, it is faster to follow the primary key full scan index

this is the choice of MYSQL query optimizer. If you think the query optimizer does not choose the optimal index, you can use force index

.

Previous: Framework7's popup won't shut down?

Next: Is this a problem with the MySQL driver version?

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1eace6b-4f5e2.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1eace6b-4f5e2.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?