Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Not a Bug
-
10.4.22, 10.4.26, 10.5.17
-
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
Issue Links
- relates to
-
-
- Confirmed
-
-
MDEV-32358 Improve optimizer cost model to take ICP cost (and benefits) into account
-
- Open
-