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

Optimizer choosing incorrect index in 10.6, 10.5 but not in 10.4

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.5, 10.6, 10.11
    • 10.6.18, 10.11.8
    • Optimizer
    • None

    Description

      Queries.sql has all the queries rrequired to reproduce issue.

      Please see the explain plans for various versions below

      CS 10.4.32. Uses correct index regardless of the value passed in the where clause

      MariaDB [test]> ANALYZE SELECT   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3;
      +------+-------------+-------+------+---------------+--------+---------+-------+------+---------+----------+------------+---------------------------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key    | key_len | ref   | rows | r_rows  | filtered | r_filtered | Extra                                                               |
      +------+-------------+-------+------+---------------+--------+---------+-------+------+---------+----------+------------+---------------------------------------------------------------------+
      |    1 | SIMPLE      | a     | ref  | indx01        | indx01 | 52      | const | 3000 | 3000.00 |   100.00 |     100.00 | Using index condition; Using where; Using temporary; Using filesort |
      +------+-------------+-------+------+---------------+--------+---------+-------+------+---------+----------+------------+---------------------------------------------------------------------+
      1 row in set (0.013 sec)
       
      MariaDB [test]>
      MariaDB [test]> ANALYZE SELECT   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_06' GROUP BY a.c3;
      +------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key    | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra                                                               |
      +------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
      |    1 | SIMPLE      | a     | ref  | indx01        | indx01 | 52      | const | 1    | 1.00   |   100.00 |     100.00 | Using index condition; Using where; Using temporary; Using filesort |
      +------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
      1 row in set (0.005 sec)
      

      CS 10.5.23. Uses incorrect index when value passed doesn't have good selectivity

      MariaDB [test]> ANALYZE SELECT   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3;
      +------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
      | id   | select_type | table | type  | possible_keys | key    | key_len | ref  | rows  | r_rows   | filtered | r_filtered | Extra       |
      +------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
      |    1 | SIMPLE      | a     | index | indx01        | indx02 | 4       | NULL | 14697 | 15001.00 |    20.41 |      20.00 | Using where |
      +------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
      1 row in set (0.083 sec)
       
      MariaDB [test]>
      MariaDB [test]> ANALYZE SELECT   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_06' GROUP BY a.c3;
      +------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key    | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra                                                               |
      +------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
      |    1 | SIMPLE      | a     | ref  | indx01        | indx01 | 52      | const | 1    | 1.00   |   100.00 |     100.00 | Using index condition; Using where; Using temporary; Using filesort |
      +------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
      1 row in set (0.003 sec)
      

      CS 10.6.16. Same behaviour as 10.5.23

      MariaDB [test]> ANALYZE SELECT   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3;
      +------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
      | id   | select_type | table | type  | possible_keys | key    | key_len | ref  | rows  | r_rows   | filtered | r_filtered | Extra       |
      +------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
      |    1 | SIMPLE      | a     | index | indx01        | indx02 | 4       | NULL | 14700 | 15001.00 |    20.41 |      20.00 | Using where |
      +------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
      1 row in set (0.098 sec)
       
      MariaDB [test]>
      MariaDB [test]> ANALYZE SELECT   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_06' GROUP BY a.c3;
      +------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key    | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra                                                               |
      +------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
      |    1 | SIMPLE      | a     | ref  | indx01        | indx01 | 52      | const | 1    | 1.00   |   100.00 |     100.00 | Using index condition; Using where; Using temporary; Using filesort |
      +------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
      1 row in set (0.005 sec)
      

      Ver 10.6.16. using FORCE INDEX shows how it would scan less rows for the same query if it uses the 'correct' index. The same index that it automatically selects in 10.4

      MariaDB [test]> ANALYZE SELECT   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a FORCE INDEX (indx02)  WHERE a.c2='c2_01' GROUP BY a.c3;
      +------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
      | id   | select_type | table | type  | possible_keys | key    | key_len | ref  | rows  | r_rows   | filtered | r_filtered | Extra       |
      +------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
      |    1 | SIMPLE      | a     | index | NULL          | indx02 | 4       | NULL | 14700 | 15001.00 |   100.00 |      20.00 | Using where |
      +------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
      

      Attachments

        1. MDEV-33306.test
          629 kB
          Alice Sherepa
        2. Queries.sql
          2 kB
          Susmeet Khaire

        Issue Links

          Activity

            People

              monty Michael Widenius
              susmeet.khaire Susmeet Khaire
              Votes:
              1 Vote for this issue
              Watchers:
              10 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.