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

explain may lie about index being used when two or more indexes start with same column

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 10.5.17
    • Fix Version/s: N/A
    • Component/s: Server
    • Labels:
      None

      Description

      In some circumstances EXPLAIN does not report the correct index being chosen for a given query. It seems to happen when there are more than one composite index that start with the same column(s).

      This can be seen in the optimizer trace.

      MariaDB [test]> CREATE TABLE `tritest` (
        `Id` bigint(20) NOT NULL AUTO_INCREMENT,
        `IdDispositivo` int(11) DEFAULT NULL,
        `TipoEvento` int(11) DEFAULT NULL,
        `Fecha` bigint(20) DEFAULT NULL,
        PRIMARY KEY (`Id`),
        KEY `TipoEvento` (`TipoEvento`),
        KEY `IdDispositivo` (`IdDispositivo`,`TipoEvento`,`Fecha`),
        KEY `IdDispositivo_2` (`IdDispositivo`,`Fecha`)
      ) ENGINE=InnoDB AUTO_INCREMENT=26952849249 DEFAULT CHARSET=latin1;
      Query OK, 0 rows affected (0.000 sec)
       
      [ LOAD ATTACHED DUMP ]
       
      MariaDB [test]> set optimizer_trace=1;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> explain select Id from tritest where IdDispositivo = 707043  AND Fecha >= 1665126000000 order by Fecha DESC, Id DESC LIMIT 0,1\G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: tritest
               type: ref
      possible_keys: IdDispositivo,IdDispositivo_2
                key: IdDispositivo
            key_len: 5
                ref: const
               rows: 1
              Extra: Using where; Using index; Using filesort
      1 row in set (0.000 sec)
       
      MariaDB [test]> select Id from tritest where IdDispositivo = 707043  AND Fecha >= 1665126000000 order by Fecha DESC, Id DESC LIMIT 0,1\G
      *************************** 1. row ***************************
      Id: 26952846959
      1 row in set (0.000 sec)
       
      MariaDB [test]> select * from information_schema.optimizer_trace\G
      *************************** 1. row ***************************
                                  QUERY: select Id from tritest where IdDispositivo = 707043  AND Fecha >= 1665126000000 order by Fecha DESC, Id DESC LIMIT 0,1
                                  TRACE: {
        "steps": [
          {
            "join_preparation": {
              "select_id": 1,
              "steps": [
                {
                  "expanded_query": "select tritest.`Id` AS `Id` from tritest where tritest.IdDispositivo = 707043 and tritest.Fecha >= 1665126000000 order by tritest.Fecha desc,tritest.`Id` desc limit 0,1"
                }
              ]
            }
          },
          {
            "join_optimization": {
              "select_id": 1,
              "steps": [
                {
                  "condition_processing": {
                    "condition": "WHERE",
                    "original_condition": "tritest.IdDispositivo = 707043 and tritest.Fecha >= 1665126000000",
                    "steps": [
                      {
                        "transformation": "equality_propagation",
                        "resulting_condition": "tritest.Fecha >= 1665126000000 and multiple equal(707043, tritest.IdDispositivo)"
                      },
                      {
                        "transformation": "constant_propagation",
                        "resulting_condition": "tritest.Fecha >= 1665126000000 and multiple equal(707043, tritest.IdDispositivo)"
                      },
                      {
                        "transformation": "trivial_condition_removal",
                        "resulting_condition": "tritest.Fecha >= 1665126000000 and multiple equal(707043, tritest.IdDispositivo)"
                      }
                    ]
                  }
                },
                {
                  "table_dependencies": [
                    {
                      "table": "tritest",
                      "row_may_be_null": false,
                      "map_bit": 0,
                      "depends_on_map_bits": []
                    }
                  ]
                },
                {
                  "ref_optimizer_key_uses": [
                    {
                      "table": "tritest",
                      "field": "IdDispositivo",
                      "equals": "707043",
                      "null_rejecting": true
                    },
                    {
                      "table": "tritest",
                      "field": "IdDispositivo",
                      "equals": "707043",
                      "null_rejecting": true
                    }
                  ]
                },
                {
                  "rows_estimation": [
                    {
                      "table": "tritest",
                      "range_analysis": {
                        "table_scan": {
                          "rows": 1000,
                          "cost": 207
                        },
                        "potential_range_indexes": [
                          {
                            "index": "PRIMARY",
                            "usable": false,
                            "cause": "not applicable"
                          },
                          {
                            "index": "TipoEvento",
                            "usable": false,
                            "cause": "not applicable"
                          },
                          {
                            "index": "IdDispositivo",
                            "usable": true,
                            "key_parts": ["IdDispositivo", "TipoEvento", "Fecha", "Id"]
                          },
                          {
                            "index": "IdDispositivo_2",
                            "usable": true,
                            "key_parts": ["IdDispositivo", "Fecha", "Id"]
                          }
                        ],
                        "best_covering_index_scan": {
                          "index": "IdDispositivo_2",
                          "cost": 202.2685383,
                          "chosen": true
                        },
                        "setup_range_conditions": [],
                        "analyzing_range_alternatives": {
                          "range_scan_alternatives": [
                            {
                              "index": "IdDispositivo",
                              "ranges": ["(707043) <= (IdDispositivo) <= (707043)"],
                              "rowid_ordered": false,
                              "using_mrr": false,
                              "index_only": true,
                              "rows": 1,
                              "cost": 0.34532957,
                              "chosen": true
                            },
                            {
                              "index": "IdDispositivo_2",
                              "ranges": [
                                "(707043,1665126000000) <= (IdDispositivo,Fecha) <= (707043)"
                              ],
                              "rowid_ordered": false,
                              "using_mrr": false,
                              "index_only": true,
                              "rows": 1,
                              "cost": 0.345268538,
                              "chosen": true
                            }
                          ],
                          "analyzing_roworder_intersect": {
                            "cause": "too few roworder scans"
                          },
                          "analyzing_index_merge_union": []
                        },
                        "group_index_range": {
                          "chosen": false,
                          "cause": "no group by or distinct"
                        },
                        "chosen_range_access_summary": {
                          "range_access_plan": {
                            "type": "range_scan",
                            "index": "IdDispositivo_2",
                            "rows": 1,
                            "ranges": [
                              "(707043,1665126000000) <= (IdDispositivo,Fecha) <= (707043)"
                            ]
                          },
                          "rows_for_plan": 1,
                          "cost_for_plan": 0.345268538,
                          "chosen": true
                        }
                      }
                    },
                    {
                      "table": "tritest",
                      "rowid_filters": [
                        {
                          "key": "IdDispositivo_2",
                          "build_cost": 0.130268538,
                          "rows": 1
                        },
                        {
                          "key": "IdDispositivo_2",
                          "build_cost": 0.130268538,
                          "rows": 1
                        }
                      ]
                    },
                    {
                      "selectivity_for_indexes": [
                        {
                          "index_name": "IdDispositivo_2",
                          "selectivity_from_index": 0.001
                        }
                      ],
                      "selectivity_for_columns": [],
                      "cond_selectivity": 0.001
                    }
                  ]
                },
                {
                  "considered_execution_plans": [
                    {
                      "plan_prefix": [],
                      "table": "tritest",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "ref",
                            "index": "IdDispositivo",
                            "used_range_estimates": true,
                            "rows": 1,
                            "cost": 0.12532957,
                            "chosen": true
                          },
                          {
                            "access_type": "ref",
                            "index": "IdDispositivo_2",
                            "rows": 1,
                            "cost": 1.000268538,
                            "chosen": false,
                            "cause": "cost"
                          },
                          {
                            "access_type": "range",
                            "resulting_rows": 1,
                            "cost": 0.345268538,
                            "chosen": false
                          }
                        ],
                        "chosen_access_method": {
                          "type": "ref",
                          "records": 1,
                          "cost": 0.12532957,
                          "uses_join_buffering": false
                        }
                      },
                      "rows_for_plan": 1,
                      "cost_for_plan": 0.32532957,
                      "estimated_join_cardinality": 1
                    }
                  ]
                },
                {
                  "best_join_order": ["tritest"]
                },
                {
                  "attaching_conditions_to_tables": {
                    "original_condition": "tritest.IdDispositivo = 707043 and tritest.Fecha >= 1665126000000",
                    "attached_conditions_computation": [],
                    "attached_conditions_summary": [
                      {
                        "table": "tritest",
                        "attached": "tritest.Fecha >= 1665126000000"
                      }
                    ]
                  }
                },
                {
                  "reconsidering_access_paths_for_index_ordering": {
                    "clause": "ORDER BY",
                    "fanout": 1,
                    "read_time": 0.12632957,
                    "table": "tritest",
                    "rows_estimation": 1,
                    "possible_keys": [
                      {
                        "index": "PRIMARY",
                        "can_resolve_order": false,
                        "cause": "not usable index for the query"
                      },
                      {
                        "index": "TipoEvento",
                        "can_resolve_order": false,
                        "cause": "not usable index for the query"
                      },
                      {
                        "index": "IdDispositivo",
                        "can_resolve_order": false,
                        "cause": "order can not be resolved by key"
                      },
                      {
                        "index": "IdDispositivo_2",
                        "can_resolve_order": true,
                        "updated_limit": 1000,
                        "range_scan_time": 0.345268538,
                        "index_scan_time": 0.345268538,
                        "usable": false,
                        "cause": "cost"
                      }
                    ]
                  }
                }
              ]
            }
          },
          {
            "join_execution": {
              "select_id": 1,
              "steps": []
            }
          }
        ]
      }
      MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
                INSUFFICIENT_PRIVILEGES: 0
      1 row in set (0.000 sec)
      

      You can see above that EXPLAIN says the chosen index will be IdDispositivo, but in reality IdDispositivo2 is used as it has a lower cost.

      Reproducible with attached dump.

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            rpizzi Rick Pizzi
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.