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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.5, 10.6, 10.11
    • 10.11.8, 10.6.18
    • 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

        Issue Links

          Activity

            Transition Time In Source Status Execution Times
            Michael Widenius made transition -
            Open In Progress
            15d 15h 4m 1
            Michael Widenius made transition -
            In Progress In Testing
            6s 1
            Michael Widenius made transition -
            In Testing Closed
            2d 23h 13m 1

            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.