Databases Reference
In-Depth Information
select_type: SIMPLE
table: inventory
type: index
possible_keys: NULL
key: idx_store_id_film_id
key_len: 3
ref: NULL
rows: 4673
Extra:
Using index
Index-covered queries have subtleties that can disable this optimization. The MySQL
query optimizer decides before executing a query whether an index covers it. Suppose
the index covers a
WHERE
condition, but not the entire query. If the condition evaluates
as false, MySQL 5.5 and earlier will fetch the row anyway, even though it doesn't need
it and will filter it out.
Let's see why this can happen, and how to rewrite the query to work around the prob-
lem. We begin with the following query:
mysql>
EXPLAIN SELECT * FROM products WHERE actor='SEAN CARREY'
->
AND title like '%APOLLO%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: products
type: ref
possible_keys: ACTOR,IX_PROD_ACTOR
key: ACTOR
key_len: 52
ref: const
rows: 10
Extra: Using where
The index can't cover this query for two reasons:
• No index covers the query, because we selected all columns from the table and no
index covers all columns. There's still a shortcut MySQL could theoretically use,
though: the
WHERE
clause mentions only columns the index covers, so MySQL could
use the index to find the actor and check whether the title matches, and only then
read the full row.
• MySQL can't perform the
LIKE
operation in the index. This is a limitation of the
low-level storage engine API, which in MySQL 5.5 and earlier allows only simple
comparisons (such as equality, inequality, and greater-than) in index operations.
MySQL can perform prefix-match
LIKE
patterns in the index because it can convert
them to simple comparisons, but the leading wildcard in the query makes it im-
possible for the storage engine to evaluate the match. Thus, the MySQL server itself
will have to fetch and match on the row's values, not the index's values.
There's a way to work around both problems with a combination of clever indexing
and query rewriting. We can extend the index to cover
(artist, title, prod_id)
and
rewrite the query as follows:
