Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0, 10.1, 10.1.24, 10.2.9, 10.2
-
None
Description
MariaDB 10.1.x and 10.2.x do not apply " Select tables optimized away" optimization when MAX(col) value is assigned to a user variable. Consider the following primitive test case:
openxs@ao756:~/dbs/maria10.2$ bin/mysql -uroot 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 8
|
Server version: 10.2.9-MariaDB Source distribution
|
|
|
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
MariaDB [test]> create table test(a int, key(a));
|
Query OK, 0 rows affected (0.32 sec)
|
|
|
MariaDB [test]> insert into test values (1), (2), (3);
|
Query OK, 3 rows affected (0.14 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> explain select max(a) from test;
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [test]> explain select @a := max(a) from test;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | test | index | NULL | a | 5 | NULL | 3 | Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [test]> explain select max(a) into @a from test;
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
1 row in set (0.00 sec)
|
Note that SELECT MAX(col) INTO @a FROM t is NOT affected.
With any large table this leads to huge difference in query execution time, as full index scan is slow.
Upstream MySQL 5.6.x is also not affected:
mysql> create table test(a int, key(a));
|
Query OK, 0 rows affected (0.70 sec)
|
|
|
mysql> insert into test values (1), (2), (3);
|
Query OK, 3 rows affected (0.26 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
|
mysql> explain select max(a) from test;
|
+----+-------------+-------+------+---------------+------+---------+------+-----
|
-+------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
|
| Extra |
|
+----+-------------+-------+------+---------------+------+---------+------+-----
|
-+------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL
|
| Select tables optimized away |
|
+----+-------------+-------+------+---------------+------+---------+------+-----
|
-+------------------------------+
|
1 row in set (0.12 sec)
|
|
|
mysql> explain select @id := max(a) from test;
|
+----+-------------+-------+------+---------------+------+---------+------+-----
|
-+------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
|
| Extra |
|
+----+-------------+-------+------+---------------+------+---------+------+-----
|
-+------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL
|
| Select tables optimized away |
|
+----+-------------+-------+------+---------------+------+---------+------+-----
|
-+------------------------------+
|
1 row in set (0.02 sec)
|
|
|
mysql> show create table test\G
|
*************************** 1. row ***************************
|
Table: test
|
Create Table: CREATE TABLE `test` (
|
`a` int(11) DEFAULT NULL,
|
KEY `a` (`a`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
1 row in set (0.03 sec)
|
|
|
mysql> select version();
|
+------------+
|
| version() |
|
+------------+
|
| 5.6.29-log |
|
+------------+
|
1 row in set (0.04 sec)
|