Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.17, 10.2(EOL), 10.3(EOL)
-
ubuntu server 16.04 64bit
Description
“condition_pushdown_for_derived” optimization correctly use index withing a simple SELECT query, but don't use it if the result records are inserted into an existing table.
Steps to reproduce:
CREATE TABLE `tbl` (
`a` INT(11) NULL DEFAULT NULL,
`b` INT(11) NULL DEFAULT NULL,
INDEX `a` (`a`)
);
INSERT INTO `tbl` (`a`, `b`) VALUES
(1, 1),
(1, 2),
(2, 1),
(2, 2);
CREATE TABLE `tbl2` (
`a` INT(11) NULL DEFAULT NULL,
`b` INT(11) NULL DEFAULT NULL
);
Simple select query:
EXPLAIN
SELECT * FROM
(SELECT a,b FROM tbl GROUP BY a) t
WHERE a=1;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 2 | DERIVED | tbl | ref | a | a | 5 | const | 2 |
Adding INSERT INTO index is no more used:
EXPLAIN
INSERT INTO tbl2
SELECT * FROM
(SELECT a,b FROM tbl GROUP BY a) t
WHERE a=1;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
| 2 | DERIVED | tbl | ALL | NULL | NULL | NULL | NULL | 4 | Using temporary; Using filesort |
For comparison, i tried a query without GROUP BY (in this case the optimization involved is derived_merge), and the optimization is maintained when using INSERT INTO
EXPLAIN
SELECT * FROM
(SELECT * FROM tbl) t
WHERE a=1;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | tbl | ref | a | a | 5 | const | 2 |
EXPLAIN
INSERT INTO tbl2
SELECT * FROM
(SELECT * FROM tbl) t
WHERE a=1;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | tbl | ref | a | a | 5 | const | 2 |
Thanks for the report!
Reproducible with MyIsam, not with Innodb, Mariadb 10.2, 10.3
CREATE TABLE t1 (a int ,b int)engine=myisam;INSERT INTO t1 (a, b) VALUES (1, 1), (1, 2), (2, 1), (2, 2);CREATE or replace TABLE t2 (a int, b int)engine=myisam;explain extended SELECT * FROM (SELECT a,b FROM t1 GROUP BY a) t WHERE a=1;explain extended INSERT INTO t2 SELECT * FROM (SELECT a,b FROM t1 GROUP BY a) t WHERE a=1;MariaDB [test]> explain extended SELECT * FROM (SELECT a,b FROM t1 GROUP BY a) t WHERE a=1;+------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where || 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where |+------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)Note (Code 1003): select `t`.`a` AS `a`,`t`.`b` AS `b` from (select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` = 1 group by `test`.`t1`.`a`) `t` where `t`.`a` = 1MariaDB [test]> explain extended INSERT INTO t2 SELECT * FROM (SELECT a,b FROM t1 GROUP BY a) t WHERE a=1;+------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where || 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using temporary; Using filesort |+------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+2 rows in set (0.00 sec)