Details
Critical Description
Consider the following simple table:
Yuliyas-Air:maria10.6 Valerii$ bin/mysql test
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 3
|
Server version: 10.6.18-MariaDB MariaDB Server
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
MariaDB [test]> select * from tt1;
|
+------+------+------+
|
| id | c1 | c2 |
|
+------+------+------+
|
| 1 | 3 | 1 |
|
| 2 | 2 | 3 |
|
+------+------+------+
|
2 rows in set (0.014 sec)
|
|
|
MariaDB [test]> show create table tt1\G
|
*************************** 1. row ***************************
|
Table: tt1
|
Create Table: CREATE TABLE `tt1` (
|
`id` int(11) DEFAULT NULL,
|
`c1` int(11) DEFAULT NULL,
|
`c2` varchar(100) DEFAULT NULL,
|
KEY `c1` (`c1`),
|
KEY `c2` (`c2`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
1 row in set (0.004 sec)
|
|
|
MariaDB [test]> insert into tt1 select * from tt1;
|
Query OK, 2 rows affected (0.016 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> insert into tt1 select * from tt1;
|
Query OK, 4 rows affected (0.003 sec)
|
Records: 4 Duplicates: 0 Warnings: 0
|
|
|
...
|
|
|
MariaDB [test]> insert into tt1 select * from tt1;
|
Query OK, 32768 rows affected (0.297 sec)
|
Records: 32768 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> update tt1 set c1 = 4 limit 1;
|
Query OK, 1 row affected (0.012 sec)
|
Rows matched: 1 Changed: 1 Warnings: 0
|
|
|
MariaDB [test]> select c1, count(*) from tt1 group by c1;
|
+------+----------+
|
| c1 | count(*) |
|
+------+----------+
|
| 2 | 32768 |
|
| 3 | 32767 |
|
| 4 | 1 |
|
+------+----------+
|
3 rows in set (0.042 sec)
|
The following queries get expected execution plans:
MariaDB [test]> explain select * from tt1 where c1 = 3;
|
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|
| 1 | SIMPLE | tt1 | ALL | c1 | NULL | NULL | NULL | 65758 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> explain select * from tt1 where c1 = 4;
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
| 1 | SIMPLE | tt1 | ref | c1 | c1 | 5 | const | 1 | |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
1 row in set (0.003 sec)
|
|
|
MariaDB [test]> explain select * from tt1 where c1 = '4';
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
| 1 | SIMPLE | tt1 | ref | c1 | c1 | 5 | const | 1 | |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|
1 row in set (0.000 sec)
|
but if we add name_const() as it happens when the query is executed from the stored routine the plan changes to use index condition pushdown:
MariaDB [test]> explain select * from tt1 where c1 = name_const('a',4);
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|
| 1 | SIMPLE | tt1 | ref | c1 | c1 | 5 | const | 1 | Using index condition |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|
1 row in set (0.004 sec)
|
|
|
MariaDB [test]> explain select * from tt1 where c1 = name_const('a','4');
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|
| 1 | SIMPLE | tt1 | ref | c1 | c1 | 5 | const | 1 | Using index condition |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|
1 row in set (0.000 sec)
|
which is strange and not expected. Moreover, we may note somewhat slower execution as a result:
MariaDB [test]> analyze format=json select * from tt1 where c1 = 4\G
|
*************************** 1. row ***************************
|
ANALYZE: {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 2.356,
|
"table": {
|
"table_name": "tt1",
|
"access_type": "ref",
|
"possible_keys": ["c1"],
|
"key": "c1",
|
"key_length": "5",
|
"used_key_parts": ["c1"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 1,
|
"r_rows": 1,
|
"r_table_time_ms": 0.203,
|
"r_other_time_ms": 0.01,
|
"r_engine_stats": {
|
"pages_accessed": 4
|
},
|
"filtered": 100,
|
"r_filtered": 100
|
}
|
}
|
}
|
1 row in set (0.004 sec)
|
|
|
MariaDB [test]> analyze format=json select * from tt1 where c1 = name_const('a',4)\G
|
*************************** 1. row ***************************
|
ANALYZE: {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 2.423,
|
"table": {
|
"table_name": "tt1",
|
"access_type": "ref",
|
"possible_keys": ["c1"],
|
"key": "c1",
|
"key_length": "5",
|
"used_key_parts": ["c1"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 1,
|
"r_rows": 1,
|
"r_table_time_ms": 2.396,
|
"r_other_time_ms": 0.02,
|
"r_engine_stats": {
|
"pages_accessed": 4
|
},
|
"filtered": 100,
|
"r_filtered": 100,
|
"index_condition": "tt1.c1 = 4"
|
}
|
}
|
}
|
1 row in set (0.003 sec)
|
Why all these happens and can we force the plans to be the same for such cases?