Optimizing
SELECT
Statements
655
This result was obtained on a Pentium II 400MHz system. It shows that MySQL can execute 1,000,000
simple addition expressions in 0.32 seconds on that system.
All MySQL functions should be highly optimized, but there may be some exceptions.
BENCHMARK()
[957]
is an excellent tool for finding out if some function is a problem for your queries.
8.3.1.1. Speed of
SELECT
Statements
In general, when you want to make a slow
SELECT ... WHERE
query faster, the first thing to check
is whether you can add an index. All references between different tables should usually be done with
indexes. You can use the
EXPLAIN
statement to determine which indexes are used for a
SELECT
. See
Section 8.2.1, “Optimizing Queries with
EXPLAIN
”
, and
Section 8.5.3, “How MySQL Uses Indexes”
.
Some general tips for speeding up queries on
MyISAM
tables:
• To help MySQL better optimize queries, use
ANALYZE TABLE
or run
myisamchk --analyze
on
a table after it has been loaded with data. This updates a value for each index part that indicates
the average number of rows that have the same value. (For unique indexes, this is always 1.)
MySQL uses this to decide which index to choose when you join two tables based on a nonconstant
expression. You can check the result from the table analysis by using
SHOW INDEX FROM
tbl_name
and examining the
Cardinality
value.
myisamchk --description --verbose
shows index distribution information.
• To sort an index and data according to an index, use
myisamchk --sort-index --sort-
records=1
(assuming that you want to sort on index 1). This is a good way to make queries faster
if you have a unique index from which you want to read all rows in order according to the index. The
first time you sort a large table this way, it may take a long time.
8.3.1.2.
WHERE
Clause Optimization
This section discusses optimizations that can be made for processing
WHERE
clauses. The examples
use
SELECT
statements, but the same optimizations apply for
WHERE
clauses in
DELETE
and
UPDATE
statements.
Work on the MySQL optimizer is ongoing, so this section is incomplete. MySQL performs a great many
optimizations, not all of which are documented here.
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)
• Constant folding:
(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(*)
[970]
on a single table without a
WHERE
is retrieved directly from the table information
for
MyISAM
and
MEMORY
tables. This is also done for any
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.
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...