I’m trying to fetch rows of a table that don’t have an ID in another table. For example, all rows of table a that don’t exist in table b:
> CREATE TABLE a (x INT PRIMARY KEY); CREATE TABLE b (y INT PRIMARY KEY);
CREATE TABLE
An explain shows:
> EXPLAIN SELECT a.* FROM a LEFT JOIN b ON a.x = b.y WHERE b.y IS NULL and a.x = 1;
+-------+--------+-------+-------------+
| Level | Type | Field | Description |
+-------+--------+-------+-------------+
| 0 | render | | |
| 1 | filter | | |
| 2 | join | | |
| 2 | | type | left outer |
| 3 | scan | | |
| 3 | | table | a@primary |
| 3 | | spans | ALL |
| 3 | scan | | |
| 3 | | table | b@primary |
| 3 | | spans | ALL |
+-------+--------+-------+-------------+
Here no index is used ever though table a should be restricted to a single row. The b.y IS NULL appears to be preventing an index from being used. Is there a better query to do this that will use an index?