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

Index choice depends on index definition order

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Critical
    • Resolution: Unresolved
    • Affects Version/s: 10.4.22, 10.4.26, 10.5.17
    • Fix Version/s: 10.4, 10.5
    • Component/s: Optimizer
    • Labels:
      None

      Description

      Consider this primitive example:

      MariaDB [test]> drop table t;
      Query OK, 0 rows affected (0.012 sec)
       
      MariaDB [test]> create table t (id int primary key, c1 int, c2 int, c3 int, c4 int, c5 int, key i1(c1), key i2(c1,c2,c3,c4));
      Query OK, 0 rows affected (0.025 sec)
       
      MariaDB [test]> explain select * from t where c1 = 1 and c2 = 1 and (c3 = 0 or c4 = 5);
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      |    1 | SIMPLE      | t     | ref  | i1,i2         | i1   | 5       | const | 1    | Using where |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> drop table t;
      Query OK, 0 rows affected (0.020 sec)
       
      MariaDB [test]> create table t (id int primary key, c1 int, c2 int, c3 int, c4 int, c5 int, key i2(c1,c2,c3,c4), key i1(c1));
      Query OK, 0 rows affected (0.029 sec)
       
      MariaDB [test]> explain select * from t where c1 = 1 and c2 = 1 and (c3 = 0 or c4 = 5);
      +------+-------------+-------+------+---------------+------+---------+-------------+------+-----------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref         | rows | Extra                 |
      +------+-------------+-------+------+---------------+------+---------+-------------+------+-----------------------+
      |    1 | SIMPLE      | t     | ref  | i2,i1         | i2   | 10      | const,const | 1    | Using index condition |
      +------+-------------+-------+------+---------------+------+---------+-------------+------+-----------------------+
      1 row in set (0.001 sec)
      

      It shows that optimizer choice depends on order of indexes definition and may be suboptimal. On real data using multiple column index may give much better performance (or not), the choice should be statistics-based and deterministic no matter in what order the indexes are added.

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            valerii Valerii Kravchuk
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

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