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    |

+--------------------------+-------+

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) |

+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------------------+