Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3
-
None
Description
The test case and diagnostic is based on https://bugs.mysql.com/bug.php?id=28188, but the difference is "t2" table of the MySQL bug has an index on column "b", and while having that index the optimization is applied correctly. Table "t2" in the following example doesn't have such an index.
CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8)); |
CREATE TABLE t2 (id int NOT NULL, b int NOT NULL); |
|
|
INSERT INTO t1 VALUES |
(1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc'); |
INSERT INTO t2 VALUES |
(3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40);
|
|
|
flush status;
|
|
|
-- Run the following SQL which doesn't imply using the 'not exists' optimization
|
-- just to get the statistics
|
SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b <> 0; |
|
|
show status like 'Handler_read%'; |
|
|
+--------------------------+-------+
|
| Variable_name | Value |
|
+--------------------------+-------+
|
| Handler_read_first | 0 |
|
| Handler_read_key | 0 |
|
| Handler_read_last | 0 |
|
| Handler_read_next | 0 |
|
| Handler_read_prev | 0 |
|
| Handler_read_retry | 0 |
|
| Handler_read_rnd | 0 |
|
| Handler_read_rnd_deleted | 0 |
|
| Handler_read_rnd_next | 16 |
|
+--------------------------+-------+
|
|
|
-- Now flush the statistics and run the SQL that implies using the 'not exists' optimization: |
flush status;
|
|
|
SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
|
|
|
show status like 'Handler_read%'; |
|
|
+--------------------------+-------+ |
| Variable_name | Value |
|
+--------------------------+-------+ |
| Handler_read_first | 0 |
|
| Handler_read_key | 0 |
|
| Handler_read_last | 0 |
|
| Handler_read_next | 0 |
|
| Handler_read_prev | 0 |
|
| Handler_read_retry | 0 |
|
| Handler_read_rnd | 0 |
|
| Handler_read_rnd_deleted | 0 |
|
| Handler_read_rnd_next | 16 |
|
+--------------------------+-------+ |
As we can see, the value of 'Handler_read_rnd_next' is the same in both cases which means no optimization has been applied though it's listed in the explain plan:
EXPLAIN SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL; |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------------------+ |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 5 | |
|
|
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 9 | Using where; Not exists; Using join buffer (flat, BNL join) | |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------------------+ |
Attachments
Issue Links
- relates to
-
-
- Closed
-