Databases Reference
In-Depth Information
select_type: PRIMARY
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: film.film_id
rows: 2
Extra: Using where; Using index
The standard advice for this query is to write it as a
LEFT OUTER JOIN
instead of using a
subquery. In theory, MySQL's execution plan will be essentially the same either way.
Let's see:
mysql>
EXPLAIN SELECT film.film_id, film.language_id
->
FROM sakila.film
->
LEFT OUTER JOIN sakila.film_actor USING(film_id)
->
WHERE film_actor.film_id IS NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where; Using index;
Not exists
