Databases Reference
In-Depth Information
UNIQUE KEY rental_date (rental_date,inventory_id,customer_id),
KEY idx_fk_inventory_id (inventory_id),
KEY idx_fk_customer_id (customer_id),
KEY idx_fk_staff_id (staff_id),
...
);
MySQL uses the
rental_date
index to order the following query, as you can see from
the lack of a filesort
13
in
EXPLAIN
:
mysql>
EXPLAIN SELECT rental_id, staff_id FROM sakila.rental
->
WHERE rental_date = '2005-05-25'
->
ORDER BY inventory_id, customer_id\G
*************************** 1. row ***************************
type: ref
possible_keys: rental_date
key: rental_date
rows: 1
Extra: Using where
This works, even though the
ORDER BY
clause isn't itself a leftmost prefix of the index,
because we specified an equality condition for the first column in the index.
Here are some more queries that can use the index for sorting. This one works because
the query provides a constant for the first column of the index and specifies an
ORDER
BY
on the second column. Taken together, those two form a leftmost prefix on the index:
... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC;
The following query also works, because the two columns in the
ORDER BY
are a leftmost
prefix of the index:
... WHERE rental_date > '2005-05-25' ORDER BY rental_date, inventory_id;
Here are some queries that
cannot
use the index for sorting:
• This query uses two different sort directions, but the index's columns are all sorted
ascending:
... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC, customer_id ASC;
• Here, the
ORDER BY
refers to a column that isn't in the index:
... WHERE rental_date = '2005-05-25' ORDER BY inventory_id, staff_id;
• Here, the
WHERE
and the
ORDER BY
don't form a leftmost prefix of the index:
... WHERE rental_date = '2005-05-25' ORDER BY customer_id;
• This query has a range condition on the first column, so MySQL doesn't use the
rest of the index:
... WHERE rental_date > '2005-05-25' ORDER BY inventory_id, customer_id;
• Here there's a multiple equality on the
inventory_id
column. For the purposes of
sorting, this is basically the same as a range:
13. MySQL calls it a “filesort,” but it doesn't necessarily use files.
