Databases Reference
In-Depth Information
Until MySQL supports general-purpose loose index scans, the workaround is to supply
a constant or list of constants for the leading columns of the index. We showed several
examples of how to get good performance with these types of queries in our indexing
case study in the previous chapter.
In MySQL 5.6, some limitations on loose index scans will be fixed with an optimizer
technique called “index condition pushdown.”
MIN() and MAX()
MySQL doesn't optimize certain
MIN()
and
MAX()
queries very well. Here's an example:
mysql>
SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = 'PENELOPE';
Because there's no index on
first_name
, this query performs a table scan. If MySQL
scans the primary key, it can theoretically stop after reading the first matching row,
because the primary key is strictly ascending and any subsequent row will have a greater
actor_id
. However, in this case MySQL will scan the whole table, which you can verify
by looking at
SHOW STATUS
counters. The workaround is to remove the
MIN()
and rewrite
the query with a
LIMIT
, as follows:
mysql>
SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY)
->
WHERE first_name = 'PENELOPE' LIMIT 1;
This general strategy often works well when MySQL would otherwise choose to scan
more rows than necessary. If you're a purist, you might object that this query is missing
the point of SQL. We're supposed to be able to tell the server
what
we want and it's
supposed to figure out
how
to get that data, whereas in this case we're telling MySQL
how
to execute the query and, as a result, it's not clear from the query that
what
we're
looking for is a minimal value. True, but sometimes you have to compromise your
principles to get high performance.
SELECT and UPDATE on the Same Table
MySQL doesn't let you
SELECT
from a table while simultaneously running an
UPDATE
on
it. This isn't really an optimizer limitation, but knowing how MySQL executes queries
can help you work around it. Here's an example of a query that's disallowed, even
though it is standard SQL. The query updates each row with the number of similar
rows in the table:
mysql>
UPDATE tbl AS outer_tbl
->
SET cnt = (
->
SELECT count(*) FROM tbl AS inner_tbl
->
WHERE inner_tbl.type = outer_tbl.type
->
);
ERROR 1093 (HY000): You can't specify target table 'outer_tbl' for update in FROM
clause
