After MySQL ORDER BY primary key id and LIMIT limit reach a certain threshold, why did you leave the primary key index instead of the expected index?

  mysql, question

Background and Phenomenon

  • The amount of data in the report_product_sales_data table is 28 million.
  • After testing, under the current data volume, when the order by PRIMARY key id and limit are up to 49, the index REPORT _ PRODUCT _ SALES _ DATA _ HQ _ CODE _ ORGZ _ ID _ INDEX can be used, and when it is greater than 49, the PRIMARY primary key index is used.

list structure

CREATE TABLE `report_product_sales_data` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `hq _ code`char (16) collatetf8 _ unicode _ ci not null comment' company code',
 `product _ ID`int (10) unsigned not null comment' commodity id',
 `orgz _ ID`int (10) unsigned not null comment 'organization id',
 `sales _ num`double (16,3) not null comment' sales quantity',
 `report _ date`datenot nullcomment' report date',
 Status' TINYINT (4) Not Null Default' 0' Comment' Status: 0. Not Daily Closed, 1. Daily Closed',
 `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 _ COMMENT =' Commodity Day Business Data Sheet';

Explain command to view execution plan

-Batch query takes 154 ms.
 select product_id, sales_num, report_date from `report_product_sales_data`
 where `hq_code` = '000030'
 and `orgz_id` = 229
 and `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_date` > '2018-05-11' order by id desc
 limit 320;
 -- explain the results as follows
 id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
 1    SIMPLE    report_product_sales_data    range    report_product_sales_data_unique,  report_product_sales_data_hq_code_orgz_id_index    report_product_sales_data_hq_code_orgz_id_index    55    NULL    37088    Using index condition;   Using where;  Using filesort
-Batch query takes 397 ms.
 select product_id, sales_num, report_date from `report_product_sales_data`
 where `hq_code` = '000030'
 and `orgz_id` = 229
 and `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_date` > '2018-05-11'
 order by `id` desc limit 10;
 -- explain the results as follows
 id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
 1    SIMPLE    report_product_sales_data    index    report_product_sales_data_unique,report_product_sales_data_hq_code_orgz_id_index    PRIMARY    4    NULL    7624    Using where

Turn on optimizer tracking to view MySQL optimization process

-Turn on optimizer tracking
 set session optimizer_trace='enabled=on';
 -After executing the query statement, you can view the specific optimizer execution process by executing the following select statement
 select * from information_schema.optimizer_trace;
-for this query that has gone through the expected index of report _ product _ sales _ data _ HQ _ code _ orgz _ id _ index, let's look at the execution process of the optimizer.
 select product_id, sales_num, report_date from `report_product_sales_data`
 where `hq_code` = '000030'
 and `orgz_id` = 229
 and `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_date` > '2018-05-11' order by id desc
 limit 320;
-- 看下trace部分
{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ 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 320"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 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_index这个索引时cost最小
                      "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 id这边时,MySQL也没有改变执行计划,还是选择了report_product_sales_data_hq_code_orgz_id_index索引
              "clause": "ORDER BY",
              "index_order_summary": {
                "table": "`report_product_sales_data`",
                "index_provides_order": false,
                "order_direction": "undefined",
                "index": "report_product_sales_data_hq_code_orgz_id_index",
                "plan_changed": false
              }
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "desc",
                "table": "`report_product_sales_data`",
                "field": "id"
              }
            ],
            "filesort_priority_queue_optimization": {
              "limit": 320,
              "rows_estimate": 61044633,
              "row_size": 76,
              "memory_available": 262144,
              "chosen": true
            },
            "filesort_execution": [
            ],
            "filesort_summary": {
              "rows": 321,
              "examined_rows": 15768,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 26964,
              "sort_mode": "<sort_key, additional_fields>"
            }
          }
        ]
      }
    }
  ]
}
-For this query that went through the unexpected PRIMARY key index, let's look at the execution process of the optimizer.
 select product_id, sales_num, report_date from `report_product_sales_data`
 where `hq_code` = '000030'
 and `orgz_id` = 229
 and `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_date` > '2018-05-11' order by id desc
 limit 10;
-- 看下trace部分
{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ 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#": 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_index这个索引时cost最小
                      "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 id这边时,MySQL改变了执行计划,选择了PRIMARY主键索引
              "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#": 1,
        "steps": [
        ]
      }
    }
  ]
}

Problem

It can be seen from the phenomenon that MySQL changed its execution plan and chose the primary key index when the value of limit reached a certain critical value (in the data environment I tested, limit >= 50 would go through the primary key index), but I don’t know what the specific rules are. Ask the great god for advice, why is there such a change in the implementation plan?

Optimizer is based on the comprehensive consideration of RBO and CBO, and does not necessarily have the highest index efficiency. full scan table and full scan index sometimes have higher efficiency.

Assuming the query LIMIT 1, the data that meets the WHERE condition has just been inserted, is it faster to use the WHERE condition index or the ORDER BY id primary key? Obviously, it is faster to go through the primary key full scan index.