Thursday, January 5, 2012

Optimizing queries with EXPLAIN

The explain command gives information about indexes which are used for the query, so you could check, whether it is optimized as you expected.


The code from the example:

mysql> EXPLAIN SELECT f.film_id, f.title, c.name
> FROM film f INNER JOIN film_category fc
> ON f.film_id=fc.film_id INNER JOIN category c
> ON fc.category_id=c.category_id WHERE f.title LIKE 'T%' \G
*************************** 1. row ***************************
select_type: SIMPLE
table: c
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 16
Extra:
*************************** 2. row ***************************
select_type: SIMPLE
table: fc
type: ref
possible_keys: PRIMARY,fk_film_category_category
key: fk_film_category_category
key_len: 1
ref: sakila.c.category_id
rows: 1
Extra: Using index
*************************** 3. row ***************************
select_type: SIMPLE
table: f
type: eq_ref
possible_keys: PRIMARY,idx_title
key: PRIMARY
key_len: 2
ref: sakila.fc.film_id
rows: 1
Extra: Using where

No comments:

Post a Comment