Details
Minor
Description
This is a performance issue.
Let's create and populate tables t1, t2,t3 with the following commands:
CREATE TABLE t1 (
pk int NOT NULL,
col_int_nokey INT NOT NULL,
col_int_key INT NOT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES
(1,4,0),
(2,6,8),
(3,3,1),
(7,2,6),
(8,9,1),
(9,3,6),
(10,8,2),
(11,1,4),
(12,8,8),
(13,8,4),
(14,5,4);
CREATE TABLE t2 (
pk int NOT NULL,
col_int_nokey int NOT NULL,
col_int_key int NOT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key)
) ENGINE=MyISAM;
INSERT INTO t2 VALUES (10,8,7);
CREATE TABLE t3
SELECT grandparent1.col_int_nokey AS g1
FROM t1 AS grandparent1
WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
(SELECT parent1.col_int_key AS p1,
parent1.col_int_key AS p2
FROM t1 AS parent1
LEFT JOIN t2 AS parent2
ON parent1.col_int_nokey = parent2.col_int_key
)
AND grandparent1.col_int_key <> 3
;
Then for the query
SELECT * FROM t3
WHERE g1 NOT IN
(SELECT grandparent1.col_int_nokey AS g1
FROM t1 AS grandparent1
WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
(SELECT parent1.col_int_key AS p1,
parent1.col_int_key AS p2
FROM t1 AS parent1
LEFT JOIN t2 AS parent2
ON parent1.col_int_nokey = parent2.col_int_key
)
AND grandparent1.col_int_key <> 3
);
mysql-5.6 chooses the plan:
+----+--------------------+--------------+-------+---------------+-------------+---------+------+------+-----------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+--------------+-------+---------------+-------------+---------+------+------+-----------------------------------------------------------------+ | 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 1 | Using where | | 2 | DEPENDENT SUBQUERY | parent1 | ref | col_int_key | col_int_key | 4 | func | 2 | Using index condition; Start temporary | | 2 | DEPENDENT SUBQUERY | parent2 | index | col_int_key | col_int_key | 4 | NULL | 1 | Using where; Using index; Using join buffer (Block Nested Loop) | | 2 | DEPENDENT SUBQUERY | grandparent1 | ref | col_int_key | col_int_key | 4 | func | 2 | Using index condition; Using where; End temporary | +----+--------------------+--------------+-------+---------------+-------------+---------+------+------+-----------------------------------------------------------------+ 4 rows in set (0.03 sec)
while maria db-5.3 chooses the plan:
+----+--------------------+--------------+--------+---------------+-------------+---------+----------------------------+------+----------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+--------------+--------+---------------+-------------+---------+----------------------------+------+----------------------------------------------------------------+ | 1 | PRIMARY | t3 | system | NULL | NULL | NULL | NULL | 1 | | | 2 | DEPENDENT SUBQUERY | parent1 | ALL | col_int_key | NULL | NULL | NULL | 11 | Start temporary | | 2 | DEPENDENT SUBQUERY | parent2 | ref | col_int_key | col_int_key | 4 | test.parent1.col_int_nokey | 2 | Using index | | 2 | DEPENDENT SUBQUERY | grandparent1 | ALL | col_int_key | NULL | NULL | NULL | 11 | Using where; End temporary; Using join buffer (flat, BNL join) | +----+--------------------+--------------+--------+---------------+-------------+---------+----------------------------+------+----------------------------------------------------------------+
once the materialization flag of the optimizer switch is set to 'off'.
The first plan looks more efficient as table parent1 is accessed with an index look-up.
After an execution of the query in mysql-5.6 we have the following values of the read handler counters:
mysql> show status like '%Handler_read%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 2 | | Handler_read_key | 4 | | Handler_read_last | 0 | | Handler_read_next | 4 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 2 | +-----------------------+-------+
while after an execution of the query in mariadb-5.3 we have:
MariaDB [test]> show status like '%Handler_read%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 12 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_deleted | 0 | | Handler_read_rnd_next | 22 | +--------------------------+-------+
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 927458
Re: The optimizer does not use ref access to the tables of a dependent subquery if ref is built over outer fields.
Also filed in JIRA as
MDEV-195