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

Wrong index chosen by the optimizer for ORDER BY



    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.2.27, 10.4.20, 10.2.43, 10.4.27
    • 10.4
    • Optimizer
    • None


      With the optimizer switch:


      and optimizer_use_condition_selectivity set to 1, and task_small1 table loaded from the attachment, we get the following:

      analyze format=json SELECT task0.sys_id
      FROM   task_small1 task0
          ( task0.sys_created_on >= '2020-01-01 06:00:00'           
          AND task0.sys_created_on <= '2020-08-11 04:59:59' )       AND task0.a_ref_4 = '1d979f'  
             AND task0.sys_class_name = 'c'       ORDER  BY task0.sys_created_on DESC LIMIT  0, 20;
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 2.8276,
          "table": {
            "table_name": "task0",
            "access_type": "range",
            "possible_keys": [
            "key": "task_index_created",
            "key_length": "6",
            "used_key_parts": ["sys_created_on"],
            "r_loops": 1,
            "rows": 35807,
            "r_rows": 2004,
            "r_total_time_ms": 2.7249,
            "filtered": 22.336,
            "r_filtered": 0.998,
            "attached_condition": "task0.a_ref_4 <=> '1d979f' and task0.sys_created_on >= '2020-01-01 06:00:00' and task0.sys_created_on <= '2020-08-11 04:59:59' and task0.a_ref_4 = '1d979f' and task0.sys_class_name = 'c'"

      It should be using task_task_class_created, which is more specific and still can be used for ORDER BY. I have debugged it with optimizer tracing and in gdb, and got to the point that test_if_skip_sort_order() for some reason is getting called with release_task_ref2 key in tab->ref.key. I attempted to recreate the bug artificially with hand-crafted tables and generated data, but was not successful. The case worked properly choosing the more specific key, and test_if_skip_sort_order was getting called with tab->ref.key set to the more specific key, equivalent of task_task_class_created in this case.

      In this example, the query still runs fast in spite of using the wrong key, but this is a trimmed/obfuscated production data. On the actual production data the choice of the wrong key results in a factor of 100 slower performance.


        1. bad.test.txz
          844 kB
        2. bad.test.xz
          845 kB
        3. good.test.xz
          641 kB
        4. task_small1_dump.sql
          4.98 MB

        Issue Links



              psergei Sergei Petrunia
              spachev Sasha Pachev
              1 Vote for this issue
              7 Start watching this issue



                Git Integration

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