This section discusses optimizations that can be made for
WHERE clauses. The examples use
SELECT statements, but the same
optimizations apply for
WHERE clauses in
Because work on the MySQL optimizer is ongoing, not all of the optimizations that MySQL performs are documented here.
You might be tempted to rewrite your queries to make arithmetic operations faster, while sacrificing readability. Because MySQL does similar optimizations automatically, you can often avoid this work, and leave the query in a more understandable and maintainable form. Some of the optimizations performed by MySQL follow:
Removal of unnecessary parentheses:
((a AND b) AND c OR (((a AND b) AND (c AND d)))) -> (a AND b AND c) OR (a AND b AND c AND d)
(a<b AND b=c) AND a=5 -> b>5 AND b=c AND a=5
Constant condition removal (needed because of constant folding):
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) -> B=5 OR B=6
Constant expressions used by indexes are evaluated only once.
COUNT(*) on a single table
WHERE is retrieved directly
from the table information for
MEMORY tables. This is also done
NOT NULL expression when used
with only one table.
Early detection of invalid constant expressions. MySQL
quickly detects that some
SELECT statements are
impossible and returns no rows.
For each table in a join, a simpler
WHERE is constructed to get a fast
WHERE evaluation for the table and also
to skip rows as soon as possible.
An empty table or a table with one row.
A table that is used with a
clause on a
PRIMARY KEY or a
UNIQUE index, where all index parts
are compared to constant expressions and are defined
All of the following tables are used as constant tables:
SELECT * FROM t WHERE
primary_key=1; SELECT * FROM t1,t2 WHERE t1.
primary_key=1 AND t2.
The best join combination for joining the tables is found
by trying all possibilities. If all columns in
ORDER BY and
BY clauses come from the same table, that table
is preferred first when joining.
If there is an
ORDER BY clause and a
GROUP BY clause, or if the
ORDER BY or
contains columns from tables other than the first table in
the join queue, a temporary table is created.
If you use the
MySQL uses an in-memory temporary table.
Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.
In some cases, MySQL can read rows from the index without even consulting the data file. If all columns used from the index are numeric, only the index tree is used to resolve the query.
Before each row is output, those that do not match the
HAVING clause are skipped.
Some examples of queries that are very fast:
SELECT COUNT(*) FROM
tbl_name; SELECT MIN(
tbl_name; SELECT MAX(
constant; SELECT ... FROM
key_part2,... LIMIT 10; SELECT ... FROM
key_part2DESC, ... LIMIT 10;
MySQL resolves the following queries using only the index tree, assuming that the indexed columns are numeric:
val; SELECT COUNT(*) FROM
The following queries use indexing to retrieve the rows in sorted order without a separate sorting pass:
SELECT ... FROM
key_part2,... ; SELECT ... FROM
key_part2DESC, ... ;
Copyright © 1997, 2015, Oracle and/or its affiliates. All rights reserved. Legal Notices