Query Performance Optimization - High Performance MySQL - page 206

key: idx_fk_film_id
key_len: 2
ref: const
rows: 10
Extra:
EXPLAIN shows that MySQL estimated it needed to access only 10 rows. In other words,
the query optimizer knew the chosen access type could satisfy the query efficiently.
What would happen if there were no suitable index for the query? MySQL would have
to use a less optimal access type, as we can see if we drop the index and run the query
again:
mysql> ALTER TABLE sakila.film_actor DROP FOREIGN KEY fk_film_actor_film;
mysql> ALTER TABLE sakila.film_actor DROP KEY idx_fk_film_id;
mysql> EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5073
Extra: Using where
Predictably, the access type has changed to a full table scan ( ALL ), and MySQL now
estimates it'll have to examine 5,073 rows to satisfy the query. The “Using where” in
the Extra column shows that the MySQL server is using the WHERE clause to discard
rows after the storage engine reads them.
In general, MySQL can apply a WHERE clause in three ways, from best to worst:
• Apply the conditions to the index lookup operation to eliminate nonmatching
rows. This happens at the storage engine layer.
• Use a covering index (“Using index” in the Extra column) to avoid row accesses,
and filter out nonmatching rows after retrieving each result from the index. This
happens at the server layer, but it doesn't require reading rows from the table.
• Retrieve rows from the table, then filter nonmatching rows (“Using where” in the
Extra column). This happens at the server layer and requires the server to read rows
from the table before it can filter them.
This example illustrates how important it is to have good indexes. Good indexes help
your queries get a good access type and examine only the rows they need. However,
adding an index doesn't always mean that MySQL will access and return the same
number of rows. For example, here's a query that uses the COUNT() aggregate function: 3
mysql> SELECT actor_id, COUNT(*) FROM sakila.film_actor GROUP BY actor_id;
3. See “Optimizing COUNT() Queries” on page 241 for more on this topic.