Table of Contents
This chapter explains how to optimize MySQL performance and provides examples. Optimization involves configuring, tuning, and measuring performance, at several levels. Depending on your job role (developer, DBA, or a combination of both), you might optimize at the level of individual SQL statements, entire applications, a single database server, or multiple networked database servers. Sometimes you can be proactive and plan in advance for performance, while other times you might troubleshoot a configuration or code issue after a problem occurs. Optimizing CPU and memory usage can also improve scalability, allowing the database to handle more load without slowing down.
Database performance depends on several factors at the database level, such as tables, queries, and configuration settings. These software constructs result in CPU and I/O operations at the hardware level, which you must minimize and make as efficient as possible. As you work on database performance, you start by learning the high-level rules and guidelines for the software side, and measuring performance using wall-clock time. As you become an expert, you learn more about what happens internally, and start measuring things such as CPU cycles and I/O operations.
Typical users aim to get the best database performance out of their existing software and hardware configurations. Advanced users look for opportunities to improve the MySQL software itself, or develop their own storage engines and hardware appliances to expand the MySQL ecosystem.
The most important factor in making a database application fast is its basic design:
Are the tables structured properly? In particular, do the columns have the right data types, and does each table have the appropriate columns for the type of work? For example, applications that perform frequent updates often have many tables with few columns, while applications that analyze large amounts of data often have few tables with many columns.
Are the right indexes in place to make queries efficient?
Are you using the appropriate storage engine for each table,
and taking advantage of the strengths and features of each
storage engine you use? In particular, the choice of a
transactional storage engine such as
InnoDB
or a nontransactional one such as
MyISAM
can be very important for performance and scalability.
In MySQL 5.5 and higher, InnoDB is the
default storage engine for new tables. In practice, the
advanced InnoDB performance features mean
that InnoDB tables often outperform the
simpler MyISAM tables, especially for a
busy database.
Does each table use an appropriate row format? This choice
also depends on the storage engine used for the table. In
particular, compressed tables use less disk space and so
require less disk I/O to read and write the data. Compression
is available for all kinds of workloads with
InnoDB tables, and for read-only
MyISAM tables.
Does the application use an appropriate
locking strategy? For
example, by allowing shared access when possible so that
database operations can run concurrently, and requesting
exclusive access when appropriate so that critical operations
get top priority. Again, the choice of storage engine is
significant. The InnoDB storage engine
handles most locking issues without involvement from you,
allowing for better concurrency in the database and reducing
the amount of experimentation and tuning for your code.
Are all memory areas used
for caching sized correctly? That is, large enough to
hold frequently accessed data, but not so large that they
overload physical memory and cause paging. The main memory
areas to configure are the InnoDB buffer
pool, the MyISAM key cache, and the MySQL
query cache.
Any database application eventually hits hardware limits as the database becomes more and more busy. A DBA must evaluate whether it is possible to tune the application or reconfigure the server to avoid these bottlenecks, or whether more hardware resources are required. System bottlenecks typically arise from these sources:
Disk seeks. It takes time for the disk to find a piece of data. With modern disks, the mean time for this is usually lower than 10ms, so we can in theory do about 100 seeks a second. This time improves slowly with new disks and is very hard to optimize for a single table. The way to optimize seek time is to distribute the data onto more than one disk.
Disk reading and writing. When the disk is at the correct position, we need to read or write the data. With modern disks, one disk delivers at least 10–20MB/s throughput. This is easier to optimize than seeks because you can read in parallel from multiple disks.
CPU cycles. When the data is in main memory, we must process it to get our result. Having large tables compared to the amount of memory is the most common limiting factor. But with small tables, speed is usually not the problem.
Memory bandwidth. When the CPU needs more data than can fit in the CPU cache, main memory bandwidth becomes a bottleneck. This is an uncommon bottleneck for most systems, but one to be aware of.
To use performance-oriented SQL extensions in a portable MySQL
program, you can wrap MySQL-specific keywords in a statement
within /*! */ comment delimiters. Other SQL
servers ignore the commented keywords. For information about
writing comments, see Section 9.6, “Comment Syntax”.
The core logic of a database application is performed through SQL statements, whether issued directly through an interpreter or submitted behind the scenes through an API. The tuning guidelines in this section help to speed up all kinds of MySQL applications. The guidelines cover SQL operations that read and write data, the behind-the-scenes overhead for SQL operations in general, and operations used in specific scenarios such as database monitoring.
Queries, in the form of SELECT
statements, perform all the lookup operations in the database.
Tuning these statements is a top priority, whether to achieve
sub-second response times for dynamic web pages, or to chop
hours off the time to generate huge overnight reports.
Besides SELECT statements, the tuning
techniques for queries also apply to constructs such as
CREATE TABLE...AS SELECT, INSERT
INTO...SELECT, and WHERE clauses in
DELETE statements. Those
statements have additional performance considerations because
they combine write operations with the read-oriented query
operations.
In MySQL Cluster NDB 7.2 and later, the
NDB storage engine supports a join
pushdown optimization whereby a qualifying join is sent in its
entirety to MySQL Cluster data nodes, where it can be
distributed among them and executed in parallel. For more
information about this optimization, see
Conditions for NDB pushdown joins.
The main considerations for optimizing queries are:
To make a slow SELECT ... WHERE query
faster, the first thing to check is whether you can add an
index. Set up indexes on
columns used in the WHERE clause, to
speed up evaluation, filtering, and the final retrieval of
results. To avoid wasted disk space, construct a small set
of indexes that speed up many related queries used in your
application.
Indexes are especially important for queries that
reference different tables, using features such as
joins and
foreign keys. You
can use the EXPLAIN
statement to determine which indexes are used for a
SELECT. See
Section 8.3.1, “How MySQL Uses Indexes” and
Section 8.8.1, “Optimizing Queries with EXPLAIN”.
Isolate and tune any part of the query, such as a function call, that takes excessive time. Depending on how the query is structured, a function could be called once for every row in the result set, or even once for every row in the table, greatly magnifying any inefficiency.
Minimize the number of full table scans in your queries, particularly for big tables.
Keep table statistics up to date by using the
ANALYZE TABLE statement
periodically, so the optimizer has the information needed
to construct an efficient execution plan.
Learn the tuning techniques, indexing techniques, and
configuration parameters that are specific to the storage
engine for each table. Both InnoDB and
MyISAM have sets of guidelines for
enabling and sustaining high performance in queries. For
details, see Section 8.5.5, “Optimizing InnoDB Queries”
and Section 8.6.1, “Optimizing MyISAM Queries”.
Avoid transforming the query in ways that make it hard to understand, especially if the optimizer does some of the same transformations automatically.
If a performance issue is not easily solved by one of the
basic guidelines, investigate the internal details of the
specific query by reading the
EXPLAIN plan and adjusting
your indexes, WHERE clauses, join
clauses, and so on. (When you reach a certain level of
expertise, reading the
EXPLAIN plan might be your
first step for every query.)
Adjust the size and properties of the memory areas that
MySQL uses for caching. With efficient use of the
InnoDB
buffer pool,
MyISAM key cache, and the MySQL query
cache, repeated queries run faster because the results are
retrieved from memory the second and subsequent times.
Even for a query that runs fast using the cache memory areas, you might still optimize further so that they require less cache memory, making your application more scalable. Scalability means that your application can handle more simultaneous users, larger requests, and so on without experiencing a big drop in performance.
Deal with locking issues, where the speed of your query might be affected by other sessions accessing the tables at the same time.
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.
Some examples of queries that are very fast:
SELECT COUNT(*) FROMtbl_name; SELECT MIN(key_part1),MAX(key_part1) FROMtbl_name; SELECT MAX(key_part2) FROMtbl_nameWHEREkey_part1=constant; SELECT ... FROMtbl_nameORDER BYkey_part1,key_part2,... LIMIT 10; SELECT ... FROMtbl_nameORDER BYkey_part1DESC,key_part2DESC, ... LIMIT 10;
MySQL resolves the following queries using only the entries from a secondary index, if the indexed columns are numeric:
SELECTkey_part1,key_part2FROMtbl_nameWHEREkey_part1=val; SELECT COUNT(*) FROMtbl_nameWHEREkey_part1=val1ANDkey_part2=val2; SELECTkey_part2FROMtbl_nameGROUP BYkey_part1;
The following queries use the index data to retrieve the rows in sorted order without a separate sorting pass:
SELECT ... FROMtbl_nameORDER BYkey_part1,key_part2,... ; SELECT ... FROMtbl_nameORDER BYkey_part1DESC,key_part2DESC, ... ;
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:
Because work on the MySQL optimizer is ongoing, not all of the optimizations that MySQL performs are documented here.
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(*) 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.
HAVING is merged with
WHERE if you do not use GROUP
BY or aggregate functions
(COUNT(),
MIN(), and so on).
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.
All constant tables are read first before any other tables in the query. A constant table is any of the following:
An empty table or a table with one row.
A table that is used with a WHERE
clause on a PRIMARY KEY or a
UNIQUE index, where all index parts
are compared to constant expressions and are defined
as NOT NULL.
All of the following tables are used as constant tables:
SELECT * FROM t WHEREprimary_key=1; SELECT * FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
The best join combination for joining the tables is found
by trying all possibilities. If all columns in
ORDER BY and GROUP
BY clauses come from the same table, that table
is preferred first when joining.
If there is an ORDER BY clause and a
different GROUP BY clause, or if the
ORDER BY or GROUP BY
contains columns from tables other than the first table in
the join queue, a temporary table is created.
If you use the SQL_SMALL_RESULT option,
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.
MySQL can sometimes produce query results using data from the index, without consulting the table data. If all columns used from the index are numeric, only the index data is used to resolve the query.
Before each row is output, those that do not match the
HAVING clause are skipped.
The range access method
uses a single index to retrieve a subset of table rows that
are contained within one or several index value intervals. It
can be used for a single-part or multiple-part index. The
following sections give a detailed description of how
intervals are extracted from the WHERE
clause.
For a single-part index, index value intervals can be
conveniently represented by corresponding conditions in the
WHERE clause, denoted as
range conditions
rather than “intervals.”
The definition of a range condition for a single-part index is as follows:
For both BTREE and
HASH indexes, comparison of a key
part with a constant value is a range condition when
using the
=,
<=>,
IN(),
IS NULL, or
IS NOT NULL operators.
Additionally, for BTREE indexes,
comparison of a key part with a constant value is a
range condition when using the
>,
<,
>=,
<=,
BETWEEN,
!=,
or
<>
operators, or LIKE
comparisons if the argument to
LIKE is a constant string
that does not start with a wildcard character.
For all index types, multiple range conditions combined
with OR or
AND form a range condition.
“Constant value” in the preceding descriptions means one of the following:
Here are some examples of queries with range conditions in
the WHERE clause:
SELECT * FROM t1 WHEREkey_col> 1 ANDkey_col< 10; SELECT * FROM t1 WHEREkey_col= 1 ORkey_colIN (15,18,20); SELECT * FROM t1 WHEREkey_colLIKE 'ab%' ORkey_colBETWEEN 'bar' AND 'foo';
Some nonconstant values may be converted to constants during the optimizer constant propagation phase.
MySQL tries to extract range conditions from the
WHERE clause for each of the possible
indexes. During the extraction process, conditions that
cannot be used for constructing the range condition are
dropped, conditions that produce overlapping ranges are
combined, and conditions that produce empty ranges are
removed.
Consider the following statement, where
key1 is an indexed column and
nonkey is not indexed:
SELECT * FROM t1 WHERE (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR (key1 < 'bar' AND nonkey = 4) OR (key1 < 'uux' AND key1 > 'z');
The extraction process for key key1 is as
follows:
Start with original WHERE clause:
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR (key1 < 'bar' AND nonkey = 4) OR (key1 < 'uux' AND key1 > 'z')
Remove nonkey = 4 and key1
LIKE '%b' because they cannot be used for a
range scan. The correct way to remove them is to replace
them with TRUE, so that we do not
miss any matching rows when doing the range scan. Having
replaced them with TRUE, we get:
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR (key1 < 'bar' AND TRUE) OR (key1 < 'uux' AND key1 > 'z')
Collapse conditions that are always true or false:
(key1 LIKE 'abcde%' OR TRUE) is
always true
(key1 < 'uux' AND key1 >
'z') is always false
Replacing these conditions with constants, we get:
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
Removing unnecessary TRUE and
FALSE constants, we obtain:
(key1 < 'abc') OR (key1 < 'bar')
Combining overlapping intervals into one yields the final condition to be used for the range scan:
(key1 < 'bar')
In general (and as demonstrated by the preceding example),
the condition used for a range scan is less restrictive than
the WHERE clause. MySQL performs an
additional check to filter out rows that satisfy the range
condition but not the full WHERE clause.
The range condition extraction algorithm can handle nested
AND/OR
constructs of arbitrary depth, and its output does not
depend on the order in which conditions appear in
WHERE clause.
MySQL does not support merging multiple ranges for the
range access method for
spatial indexes. To work around this limitation, you can use
a UNION with identical
SELECT statements, except
that you put each spatial predicate in a different
SELECT.
Range conditions on a multiple-part index are an extension of range conditions for a single-part index. A range condition on a multiple-part index restricts index rows to lie within one or several key tuple intervals. Key tuple intervals are defined over a set of key tuples, using ordering from the index.
For example, consider a multiple-part index defined as
key1(, and the
following set of key tuples listed in key order:
key_part1,
key_part2,
key_part3)
key_part1key_part2key_part3NULL 1 'abc' NULL 1 'xyz' NULL 2 'foo' 1 1 'abc' 1 1 'xyz' 1 2 'abc' 2 1 'aaa'
The condition key_part1
= 1
(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)
The interval covers the 4th, 5th, and 6th tuples in the preceding data set and can be used by the range access method.
By contrast, the condition
key_part3 =
'abc'
The following descriptions indicate how range conditions work for multiple-part indexes in greater detail.
For HASH indexes, each interval
containing identical values can be used. This means that
the interval can be produced only for conditions in the
following form:
key_part1 cmp const1
AND key_part2 cmp const2
AND ...
AND key_partN cmp constN;
Here, const1,
const2, … are
constants, cmp is one of the
=,
<=>,
or IS NULL comparison
operators, and the conditions cover all index parts.
(That is, there are N
conditions, one for each part of an
N-part index.) For example,
the following is a range condition for a three-part
HASH index:
key_part1= 1 ANDkey_part2IS NULL ANDkey_part3= 'foo'
For the definition of what is considered to be a constant, see Section 8.2.1.3.1, “The Range Access Method for Single-Part Indexes”.
For a BTREE index, an interval might
be usable for conditions combined with
AND, where each condition
compares a key part with a constant value using
=,
<=>,
IS NULL,
>,
<,
>=,
<=,
!=,
<>,
BETWEEN, or
LIKE
' (where
pattern''
does not start with a wildcard). An interval can be used
as long as it is possible to determine a single key
tuple containing all rows that match the condition (or
two intervals if
pattern'<>
or !=
is used).
The optimizer attempts to use additional key parts to
determine the interval as long as the comparison
operator is
=,
<=>,
or IS NULL. If the operator
is
>,
<,
>=,
<=,
!=,
<>,
BETWEEN, or
LIKE, the
optimizer uses it but considers no more key parts. For
the following expression, the optimizer uses
= from
the first comparison. It also uses
>=
from the second comparison but considers no further key
parts and does not use the third comparison for interval
construction:
key_part1= 'foo' ANDkey_part2>= 10 ANDkey_part3> 10
The single interval is:
('foo',10,-inf) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)
It is possible that the created interval contains more
rows than the initial condition. For example, the
preceding interval includes the value ('foo',
11, 0), which does not satisfy the original
condition.
If conditions that cover sets of rows contained within
intervals are combined with
OR, they form a condition
that covers a set of rows contained within the union of
their intervals. If the conditions are combined with
AND, they form a condition
that covers a set of rows contained within the
intersection of their intervals. For example, for this
condition on a two-part index:
(key_part1= 1 ANDkey_part2< 2) OR (key_part1> 5)
The intervals are:
(1,-inf) < (key_part1,key_part2) < (1,2) (5,-inf) < (key_part1,key_part2)
In this example, the interval on the first line uses one
key part for the left bound and two key parts for the
right bound. The interval on the second line uses only
one key part. The key_len column in
the EXPLAIN output
indicates the maximum length of the key prefix used.
In some cases, key_len may indicate
that a key part was used, but that might be not what you
would expect. Suppose that
key_part1 and
key_part2 can be
NULL. Then the
key_len column displays two key part
lengths for the following condition:
key_part1>= 1 ANDkey_part2< 2
But, in fact, the condition is converted to this:
key_part1>= 1 ANDkey_part2IS NOT NULL
Section 8.2.1.3.1, “The Range Access Method for Single-Part Indexes”, describes how optimizations are performed to combine or eliminate intervals for range conditions on a single-part index. Analogous steps are performed for range conditions on multiple-part indexes.
The Index Merge method
is used to retrieve rows with several
range scans and to merge
their results into one. The merge can produce unions,
intersections, or unions-of-intersections of its underlying
scans. This access method merges index scans from a single
table; it does not merge scans across multiple tables.
In EXPLAIN output, the Index
Merge method appears as
index_merge in the
type column. In this case, the
key column contains a list of indexes used,
and key_len contains a list of the longest
key parts for those indexes.
Examples:
SELECT * FROMtbl_nameWHEREkey1= 10 ORkey2= 20; SELECT * FROMtbl_nameWHERE (key1= 10 ORkey2= 20) ANDnon_key=30; SELECT * FROM t1, t2 WHERE (t1.key1IN (1,2) OR t1.key2LIKE 'value%') AND t2.key1=t1.some_col; SELECT * FROM t1, t2 WHERE t1.key1=1 AND (t2.key1=t1.some_colOR t2.key2=t1.some_col2);
The Index Merge method has several access algorithms (seen in
the Extra field of
EXPLAIN output):
Using intersect(...)
Using union(...)
Using sort_union(...)
The following sections describe these methods in greater detail.
The Index Merge optimization algorithm has the following known deficiencies:
If your query has a complex WHERE
clause with deep
AND/OR
nesting and MySQL does not choose the optimal plan, try
distributing terms using the following identity laws:
(xANDy) ORz= (xORz) AND (yORz) (xORy) ANDz= (xANDz) OR (yANDz)
Index Merge is not applicable to full-text indexes. We plan to extend it to cover these in a future MySQL release.
If a range scan is possible on some key, the optimizer will not consider using Index Merge Union or Index Merge Sort-Union algorithms. For example, consider this query:
SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;
For this query, two plans are possible:
An Index Merge scan using the (goodkey1
< 10 OR goodkey2 < 20) condition.
A range scan using the badkey <
30 condition.
However, the optimizer considers only the second plan.
The choice between different possible variants of the Index Merge access method and other access methods is based on cost estimates of various available options.
This access algorithm can be employed when a
WHERE clause was converted to several
range conditions on different keys combined with
AND, and each condition is one
of the following:
In this form, where the index has exactly
N parts (that is, all index
parts are covered):
key_part1=const1ANDkey_part2=const2... ANDkey_partN=constN
Any range condition over a primary key of an
InnoDB table.
Examples:
SELECT * FROMinnodb_tableWHEREprimary_key< 10 ANDkey_col1=20; SELECT * FROMtbl_nameWHERE (key1_part1=1 ANDkey1_part2=2) ANDkey2=2;
The Index Merge intersection algorithm performs simultaneous scans on all used indexes and produces the intersection of row sequences that it receives from the merged index scans.
If all columns used in the query are covered by the used
indexes, full table rows are not retrieved
(EXPLAIN output contains
Using index in Extra
field in this case). Here is an example of such a query:
SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;
If the used indexes do not cover all columns used in the query, full rows are retrieved only when the range conditions for all used keys are satisfied.
If one of the merged conditions is a condition over a
primary key of an InnoDB table, it is not
used for row retrieval, but is used to filter out rows
retrieved using other conditions.
The applicability criteria for this algorithm are similar to
those for the Index Merge method intersection algorithm. The
algorithm can be employed when the table's
WHERE clause was converted to several
range conditions on different keys combined with
OR, and each condition is one
of the following:
In this form, where the index has exactly
N parts (that is, all index
parts are covered):
key_part1=const1ANDkey_part2=const2... ANDkey_partN=constN
Any range condition over a primary key of an
InnoDB table.
A condition for which the Index Merge method intersection algorithm is applicable.
Examples:
SELECT * FROM t1 WHEREkey1=1 ORkey2=2 ORkey3=3; SELECT * FROMinnodb_tableWHERE (key1=1 ANDkey2=2) OR (key3='foo' ANDkey4='bar') ANDkey5=5;
This access algorithm is employed when the
WHERE clause was converted to several
range conditions combined by
OR, but for which the Index
Merge method union algorithm is not applicable.
Examples:
SELECT * FROMtbl_nameWHEREkey_col1< 10 ORkey_col2< 20; SELECT * FROMtbl_nameWHERE (key_col1> 10 ORkey_col2= 20) ANDnonkey_col=30;
The difference between the sort-union algorithm and the union algorithm is that the sort-union algorithm must first fetch row IDs for all rows and sort them before returning any rows.
This optimization improves the efficiency of direct
comparisons between a nonindexed column and a constant. In
such cases, the condition is “pushed down” to the
storage engine for evaluation. This optimization can be used
only by the NDB storage engine.
For MySQL Cluster, this optimization can eliminate the need to send nonmatching rows over the network between the cluster's data nodes and the MySQL Server that issued the query, and can speed up queries where it is used by a factor of 5 to 10 times over cases where condition pushdown could be but is not used.
Suppose that a MySQL Cluster table is defined as follows:
CREATE TABLE t1 (
a INT,
b INT,
KEY(a)
) ENGINE=NDB;
Condition pushdown can be used with queries such as the one shown here, which includes a comparison between a nonindexed column and a constant:
SELECT a, b FROM t1 WHERE b = 10;
The use of condition pushdown can be seen in the output of
EXPLAIN:
mysql> EXPLAIN SELECT a,b FROM t1 WHERE b = 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using where with pushed condition
However, condition pushdown cannot be used with either of these two queries:
SELECT a,b FROM t1 WHERE a = 10; SELECT a,b FROM t1 WHERE b + 1 = 10;
Condition pushdown is not applicable to the first query
because an index exists on column a. (An
index access method would be more efficient and so would be
chosen in preference to condition pushdown.) Condition
pushdown cannot be employed for the second query because the
comparison involving the nonindexed column
b is indirect. (However, condition pushdown
could be applied if you were to reduce b + 1 =
10 to b = 9 in the
WHERE clause.)
Condition pushdown may also be employed when an indexed column
is compared with a constant using a > or
< operator:
mysql> EXPLAIN SELECT a, b FROM t1 WHERE a < 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: range
possible_keys: a
key: a
key_len: 5
ref: NULL
rows: 2
Extra: Using where with pushed condition
Other supported comparisons for condition pushdown include the following:
column [NOT] LIKE
pattern
pattern must be a string
literal containing the pattern to be matched; for syntax,
see Section 12.5.1, “String Comparison Functions”.
column IS [NOT]
NULL
column IN
(value_list)
Each item in the value_list
must be a constant, literal value.
column BETWEEN
constant1 AND
constant2
constant1 and
constant2 must each be a
constant, literal value.
In all of the cases in the preceding list, it is possible for the condition to be converted into the form of one or more direct comparisons between a column and a constant.
Engine condition pushdown is enabled by default. To disable it
at server startup, set the
optimizer_switch system
variable. For example, in a my.cnf file,
use these lines:
[mysqld] optimizer_switch=engine_condition_pushdown=off
At runtime, disable condition pushdown like this:
SET optimizer_switch='engine_condition_pushdown=off';
Before MySQL 5.5.3, disable condition pushdown using the
engine_condition_pushdown
system variable. At server startup:
[mysqld] engine_condition_pushdown=0
At runtime, use either of these statements:
SET engine_condition_pushdown=OFF;
SET engine_condition_pushdown=0;
Limitations. Condition pushdown is subject to the following limitations:
Condition pushdown is supported only by the
NDB storage engine.
Columns may be compared with constants only; however, this includes expressions which evaluate to constant values.
Columns used in comparisons cannot be of any of the
BLOB or
TEXT types.
A string value to be compared with a column must use the same collation as the column.
Joins are not directly supported; conditions involving
multiple tables are pushed separately where possible. Use
EXPLAIN EXTENDED to
determine which conditions are actually pushed down.
MySQL can perform the same optimization on
col_name IS
NULL that it can use for
col_name =
constant_value. For example, MySQL
can use indexes and ranges to search for
NULL with IS
NULL.
Examples:
SELECT * FROMtbl_nameWHEREkey_colIS NULL; SELECT * FROMtbl_nameWHEREkey_col<=> NULL; SELECT * FROMtbl_nameWHEREkey_col=const1ORkey_col=const2ORkey_colIS NULL;
If a WHERE clause includes a
col_name IS
NULL condition for a column that is declared as
NOT NULL, that expression is optimized
away. This optimization does not occur in cases when the
column might produce NULL anyway; for
example, if it comes from a table on the right side of a
LEFT JOIN.
MySQL can also optimize the combination
col_name =
expr OR
col_name IS NULLEXPLAIN shows
ref_or_null when this
optimization is used.
This optimization can handle one IS
NULL for any key part.
Some examples of queries that are optimized, assuming that
there is an index on columns a and
b of table t2:
SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;
SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;
SELECT * FROM t1, t2
WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;
SELECT * FROM t1, t2
WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);
SELECT * FROM t1, t2
WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)
OR (t1.a=t2.a AND t2.a IS NULL AND ...);
ref_or_null works by first
doing a read on the reference key, and then a separate search
for rows with a NULL key value.
The optimization can handle only one IS
NULL level. In the following query, MySQL uses key
lookups only on the expression (t1.a=t2.a AND t2.a IS
NULL) and is not able to use the key part on
b:
SELECT * FROM t1, t2 WHERE (t1.a=t2.a AND t2.a IS NULL) OR (t1.b=t2.b AND t2.b IS NULL);
MySQL implements an A LEFT
JOIN B join_condition
Table B is set to depend on
table A and all tables on which
A depends.
Table A is set to depend on all
tables (except B) that are used
in the LEFT JOIN condition.
The LEFT JOIN condition is used to
decide how to retrieve rows from table
B. (In other words, any
condition in the WHERE clause is not
used.)
All standard join optimizations are performed, with the exception that a table is always read after all tables on which it depends. If there is a circular dependence, MySQL issues an error.
All standard WHERE optimizations are
performed.
If there is a row in A that
matches the WHERE clause, but there is
no row in B that matches the
ON condition, an extra
B row is generated with all
columns set to NULL.
If you use LEFT JOIN to find rows that
do not exist in some table and you have the following
test: col_name IS
NULLWHERE part, where
col_name is a column that is
declared as NOT NULL, MySQL stops
searching for more rows (for a particular key combination)
after it has found one row that matches the LEFT
JOIN condition.
The implementation of RIGHT JOIN is
analogous to that of LEFT JOIN with the
roles of the tables reversed.
The join optimizer calculates the order in which tables should
be joined. The table read order forced by LEFT
JOIN or STRAIGHT_JOIN helps the
join optimizer do its work much more quickly, because there
are fewer table permutations to check. Note that this means
that if you do a query of the following type, MySQL does a
full scan on b because the LEFT
JOIN forces it to be read before
d:
SELECT * FROM a JOIN b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) WHERE b.key=d.key;
The fix in this case is reverse the order in which
a and b are listed in
the FROM clause:
SELECT * FROM b JOIN a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) WHERE b.key=d.key;
For a LEFT JOIN, if the
WHERE condition is always false for the
generated NULL row, the LEFT
JOIN is changed to a normal join. For example, the
WHERE clause would be false in the
following query if t2.column1 were
NULL:
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;
Therefore, it is safe to convert the query to a normal join:
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
This can be made faster because MySQL can use table
t2 before table t1 if
doing so would result in a better query plan. To provide a
hint about the table join order, use
STRAIGHT_JOIN. (See
Section 13.2.9, “SELECT Syntax”.)
MySQL executes joins between tables using a nested-loop algorithm or variations on it.
Nested-Loop Join Algorithm
A simple nested-loop join (NLJ) algorithm reads rows from the first table in a loop one at a time, passing each row to a nested loop that processes the next table in the join. This process is repeated as many times as there remain tables to be joined.
Assume that a join between three tables t1,
t2, and t3 is to be
executed using the following join types:
Table Join Type t1 range t2 ref t3 ALL
If a simple NLJ algorithm is used, the join is processed like this:
for each row in t1 matching range {
for each row in t2 matching reference key {
for each row in t3 {
if row satisfies join conditions,
send to client
}
}
}
Because the NLJ algorithm passes rows one at a time from outer loops to inner loops, it typically reads tables processed in the inner loops many times.
Block Nested-Loop Join Algorithm
A Block Nested-Loop (BNL) join algorithm uses buffering of rows read in outer loops to reduce the number of times that tables in inner loops must be read. For example, if 10 rows are read into a buffer and the buffer is passed to the next inner loop, each row read in the inner loop can be compared against all 10 rows in the buffer. The reduces the number of times the inner table must be read by an order of magnitude.
MySQL uses join buffering under these conditions:
The join_buffer_size
system variable determines the size of each join buffer.
Join buffering can be used when the join is of type
ALL or
index (in other words,
when no possible keys can be used, and a full scan is
done, of either the data or index rows, respectively), or
range.
One buffer is allocated for each join that can be buffered, so a given query might be processed using multiple join buffers.
A join buffer is never allocated for the first nonconst
table, even if it would be of type
ALL or
index.
A join buffer is allocated prior to executing the join and freed after the query is done.
Only columns of interest to the join are stored in the join buffer, not whole rows.
For the example join described previously for the NLJ algorithm (without buffering), the join is done as follow using join buffering:
for each row in t1 matching range {
for each row in t2 matching reference key {
store used columns from t1, t2 in join buffer
if buffer is full {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions,
send to client
}
}
empty buffer
}
}
}
if buffer is not empty {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions,
send to client
}
}
}
If S is the size of each stored
t1, t2 combination is
the join buffer and C is the number
of combinations in the buffer, the number of times table
t3 is scanned is:
(S*C)/join_buffer_size + 1
The number of t3 scans decreases as the
value of join_buffer_size
increases, up to the point when
join_buffer_size is large
enough to hold all previous row combinations. At that point,
there is no speed to be gained by making it larger.
The syntax for expressing joins permits nested joins. The following discussion refers to the join syntax described in Section 13.2.9.2, “JOIN Syntax”.
The syntax of table_factor is
extended in comparison with the SQL Standard. The latter
accepts only table_reference, not a
list of them inside a pair of parentheses. This is a
conservative extension if we consider each comma in a list of
table_reference items as equivalent
to an inner join. For example:
SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
is equivalent to:
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
In MySQL, CROSS JOIN is a syntactic
equivalent to INNER JOIN (they can replace
each other). In standard SQL, they are not equivalent.
INNER JOIN is used with an
ON clause; CROSS JOIN is
used otherwise.
In general, parentheses can be ignored in join expressions containing only inner join operations. After removing parentheses and grouping operations to the left, the join expression:
t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL) ON t1.a=t2.a
transforms into the expression:
(t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3
ON t2.b=t3.b OR t2.b IS NULL
Yet, the two expressions are not equivalent. To see this,
suppose that the tables t1,
t2, and t3 have the
following state:
Table t1 contains rows
(1), (2)
Table t2 contains row
(1,101)
Table t3 contains row
(101)
In this case, the first expression returns a result set
including the rows (1,1,101,101),
(2,NULL,NULL,NULL), whereas the second
expression returns the rows (1,1,101,101),
(2,NULL,NULL,101):
mysql>SELECT *->FROM t1->LEFT JOIN->(t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)->ON t1.a=t2.a;+------+------+------+------+ | a | a | b | b | +------+------+------+------+ | 1 | 1 | 101 | 101 | | 2 | NULL | NULL | NULL | +------+------+------+------+ mysql>SELECT *->FROM (t1 LEFT JOIN t2 ON t1.a=t2.a)->LEFT JOIN t3->ON t2.b=t3.b OR t2.b IS NULL;+------+------+------+------+ | a | a | b | b | +------+------+------+------+ | 1 | 1 | 101 | 101 | | 2 | NULL | NULL | 101 | +------+------+------+------+
In the following example, an outer join operation is used together with an inner join operation:
t1 LEFT JOIN (t2, t3) ON t1.a=t2.a
That expression cannot be transformed into the following expression:
t1 LEFT JOIN t2 ON t1.a=t2.a, t3.
For the given table states, the two expressions return different sets of rows:
mysql>SELECT *->FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a;+------+------+------+------+ | a | a | b | b | +------+------+------+------+ | 1 | 1 | 101 | 101 | | 2 | NULL | NULL | NULL | +------+------+------+------+ mysql>SELECT *->FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3;+------+------+------+------+ | a | a | b | b | +------+------+------+------+ | 1 | 1 | 101 | 101 | | 2 | NULL | NULL | 101 | +------+------+------+------+
Therefore, if we omit parentheses in a join expression with outer join operators, we might change the result set for the original expression.
More exactly, we cannot ignore parentheses in the right operand of the left outer join operation and in the left operand of a right join operation. In other words, we cannot ignore parentheses for the inner table expressions of outer join operations. Parentheses for the other operand (operand for the outer table) can be ignored.
The following expression:
(t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b)
is equivalent to this expression:
t1, t2 LEFT JOIN t3 ON P(t2.b,t3.b)
for any tables t1,t2,t3 and any condition
P over attributes t2.b
and t3.b.
Whenever the order of execution of the join operations in a
join expression (join_table) is not
from left to right, we talk about nested joins. Consider the
following queries:
SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.a WHERE t1.a > 1 SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a WHERE (t2.b=t3.b OR t2.b IS NULL) AND t1.a > 1
Those queries are considered to contain these nested joins:
t2 LEFT JOIN t3 ON t2.b=t3.b t2, t3
The nested join is formed in the first query with a left join operation, whereas in the second query it is formed with an inner join operation.
In the first query, the parentheses can be omitted: The
grammatical structure of the join expression will dictate the
same order of execution for join operations. For the second
query, the parentheses cannot be omitted, although the join
expression here can be interpreted unambiguously without them.
(In our extended syntax the parentheses in (t2,
t3) of the second query are required, although
theoretically the query could be parsed without them: We still
would have unambiguous syntactical structure for the query
because LEFT JOIN and ON
would play the role of the left and right delimiters for the
expression (t2,t3).)
The preceding examples demonstrate these points:
For join expressions involving only inner joins (and not outer joins), parentheses can be removed. You can remove parentheses and evaluate left to right (or, in fact, you can evaluate the tables in any order).
The same is not true, in general, for outer joins or for outer joins mixed with inner joins. Removal of parentheses may change the result.
Queries with nested outer joins are executed in the same
pipeline manner as queries with inner joins. More exactly, a
variation of the nested-loop join algorithm is exploited.
Recall by what algorithmic schema the nested-loop join
executes a query. Suppose that we have a join query over 3
tables T1,T2,T3 of the form:
SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2)
INNER JOIN T3 ON P2(T2,T3)
WHERE P(T1,T2,T3).
Here, P1(T1,T2) and
P2(T3,T3) are some join conditions (on
expressions), whereas P(T1,T2,T3) is a
condition over columns of tables T1,T2,T3.
The nested-loop join algorithm would execute this query in the following manner:
FOR each row t1 in T1 {
FOR each row t2 in T2 such that P1(t1,t2) {
FOR each row t3 in T3 such that P2(t2,t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
}
}
}
The notation t1||t2||t3 means “a row
constructed by concatenating the columns of rows
t1, t2, and
t3.” In some of the following
examples, NULL where a row name appears
means that NULL is used for each column of
that row. For example, t1||t2||NULL means
“a row constructed by concatenating the columns of rows
t1 and t2, and
NULL for each column of
t3.”
Now let's consider a query with nested outer joins:
SELECT * FROM T1 LEFT JOIN
(T2 LEFT JOIN T3 ON P2(T2,T3))
ON P1(T1,T2)
WHERE P(T1,T2,T3).
For this query, we modify the nested-loop pattern to get:
FOR each row t1 in T1 {
BOOL f1:=FALSE;
FOR each row t2 in T2 such that P1(t1,t2) {
BOOL f2:=FALSE;
FOR each row t3 in T3 such that P2(t2,t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
f2=TRUE;
f1=TRUE;
}
IF (!f2) {
IF P(t1,t2,NULL) {
t:=t1||t2||NULL; OUTPUT t;
}
f1=TRUE;
}
}
IF (!f1) {
IF P(t1,NULL,NULL) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
}
In general, for any nested loop for the first inner table in
an outer join operation, a flag is introduced that is turned
off before the loop and is checked after the loop. The flag is
turned on when for the current row from the outer table a
match from the table representing the inner operand is found.
If at the end of the loop cycle the flag is still off, no
match has been found for the current row of the outer table.
In this case, the row is complemented by
NULL values for the columns of the inner
tables. The result row is passed to the final check for the
output or into the next nested loop, but only if the row
satisfies the join condition of all embedded outer joins.
In our example, the outer join table expressed by the following expression is embedded:
(T2 LEFT JOIN T3 ON P2(T2,T3))
For the query with inner joins, the optimizer could choose a different order of nested loops, such as this one:
FOR each row t3 in T3 {
FOR each row t2 in T2 such that P2(t2,t3) {
FOR each row t1 in T1 such that P1(t1,t2) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
}
}
}
For the queries with outer joins, the optimizer can choose only such an order where loops for outer tables precede loops for inner tables. Thus, for our query with outer joins, only one nesting order is possible. For the following query, the optimizer will evaluate two different nestings:
SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3) WHERE P(T1,T2,T3)
The nestings are these:
FOR each row t1 in T1 {
BOOL f1:=FALSE;
FOR each row t2 in T2 such that P1(t1,t2) {
FOR each row t3 in T3 such that P2(t1,t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
f1:=TRUE
}
}
IF (!f1) {
IF P(t1,NULL,NULL) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
}
and:
FOR each row t1 in T1 {
BOOL f1:=FALSE;
FOR each row t3 in T3 such that P2(t1,t3) {
FOR each row t2 in T2 such that P1(t1,t2) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
f1:=TRUE
}
}
IF (!f1) {
IF P(t1,NULL,NULL) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
}
In both nestings, T1 must be processed in
the outer loop because it is used in an outer join.
T2 and T3 are used in an
inner join, so that join must be processed in the inner loop.
However, because the join is an inner join,
T2 and T3 can be
processed in either order.
When discussing the nested-loop algorithm for inner joins, we
omitted some details whose impact on the performance of query
execution may be huge. We did not mention so-called
“pushed-down” conditions. Suppose that our
WHERE condition
P(T1,T2,T3) can be represented by a
conjunctive formula:
P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3).
In this case, MySQL actually uses the following nested-loop schema for the execution of the query with inner joins:
FOR each row t1 in T1 such that C1(t1) {
FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2) {
FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
}
}
}
You see that each of the conjuncts C1(T1),
C2(T2), C3(T3) are
pushed out of the most inner loop to the most outer loop where
it can be evaluated. If C1(T1) is a very
restrictive condition, this condition pushdown may greatly
reduce the number of rows from table T1
passed to the inner loops. As a result, the execution time for
the query may improve immensely.
For a query with outer joins, the WHERE
condition is to be checked only after it has been found that
the current row from the outer table has a match in the inner
tables. Thus, the optimization of pushing conditions out of
the inner nested loops cannot be applied directly to queries
with outer joins. Here we have to introduce conditional
pushed-down predicates guarded by the flags that are turned on
when a match has been encountered.
For our example with outer joins with:
P(T1,T2,T3)=C1(T1) AND C(T2) AND C3(T3)
the nested-loop schema using guarded pushed-down conditions looks like this:
FOR each row t1 in T1 such that C1(t1) {
BOOL f1:=FALSE;
FOR each row t2 in T2
such that P1(t1,t2) AND (f1?C2(t2):TRUE) {
BOOL f2:=FALSE;
FOR each row t3 in T3
such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) {
IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) {
t:=t1||t2||t3; OUTPUT t;
}
f2=TRUE;
f1=TRUE;
}
IF (!f2) {
IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) {
t:=t1||t2||NULL; OUTPUT t;
}
f1=TRUE;
}
}
IF (!f1 && P(t1,NULL,NULL)) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
In general, pushed-down predicates can be extracted from join
conditions such as P1(T1,T2) and
P(T2,T3). In this case, a pushed-down
predicate is guarded also by a flag that prevents checking the
predicate for the NULL-complemented row
generated by the corresponding outer join operation.
Access by key from one inner table to another in the same
nested join is prohibited if it is induced by a predicate from
the WHERE condition. (We could use
conditional key access in this case, but this technique is not
employed yet in MySQL 5.5.)
Table expressions in the FROM clause of a
query are simplified in many cases.
At the parser stage, queries with right outer joins operations are converted to equivalent queries containing only left join operations. In the general case, the conversion is performed according to the following rule:
(T1, ...) RIGHT JOIN (T2,...) ON P(T1,...,T2,...) = (T2, ...) LEFT JOIN (T1,...) ON P(T1,...,T2,...)
All inner join expressions of the form T1 INNER JOIN
T2 ON P(T1,T2) are replaced by the list
T1,T2, P(T1,T2) being
joined as a conjunct to the WHERE condition
(or to the join condition of the embedding join, if there is
any).
When the optimizer evaluates plans for join queries with outer join operation, it takes into consideration only the plans where, for each such operation, the outer tables are accessed before the inner tables. The optimizer options are limited because only such plans enables us to execute queries with outer joins operations by the nested loop schema.
Suppose that we have a query of the form:
SELECT * T1 LEFT JOIN T2 ON P1(T1,T2) WHERE P(T1,T2) AND R(T2)
with R(T2) narrowing greatly the number of
matching rows from table T2. If we executed
the query as it is, the optimizer would have no other choice
besides to access table T1 before table
T2 that may lead to a very inefficient
execution plan.
Fortunately, MySQL converts such a query into a query without
an outer join operation if the WHERE
condition is null-rejected. A condition is called
null-rejected for an outer join operation if it evaluates to
FALSE or to UNKNOWN for
any NULL-complemented row built for the
operation.
Thus, for this outer join:
T1 LEFT JOIN T2 ON T1.A=T2.A
Conditions such as these are null-rejected:
T2.B IS NOT NULL, T2.B > 3, T2.C <= T1.C, T2.B < 2 OR T2.C > 1
Conditions such as these are not null-rejected:
T2.B IS NULL, T1.B < 3 OR T2.B IS NOT NULL, T1.B < 3 OR T2.B > 3
The general rules for checking whether a condition is null-rejected for an outer join operation are simple. A condition is null-rejected in the following cases:
If it is of the form A IS NOT NULL,
where A is an attribute of any of the
inner tables
If it is a predicate containing a reference to an inner
table that evaluates to UNKNOWN when
one of its arguments is NULL
If it is a conjunction containing a null-rejected condition as a conjunct
If it is a disjunction of null-rejected conditions
A condition can be null-rejected for one outer join operation in a query and not null-rejected for another. In the query:
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
LEFT JOIN T3 ON T3.B=T1.B
WHERE T3.C > 0
the WHERE condition is null-rejected for
the second outer join operation but is not null-rejected for
the first one.
If the WHERE condition is null-rejected for
an outer join operation in a query, the outer join operation
is replaced by an inner join operation.
For example, the preceding query is replaced with the query:
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
INNER JOIN T3 ON T3.B=T1.B
WHERE T3.C > 0
For the original query, the optimizer would evaluate plans
compatible with only one access order
T1,T2,T3. For the replacing query, it
additionally considers the access sequence
T3,T1,T2.
A conversion of one outer join operation may trigger a conversion of another. Thus, the query:
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
LEFT JOIN T3 ON T3.B=T2.B
WHERE T3.C > 0
will be first converted to the query:
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
INNER JOIN T3 ON T3.B=T2.B
WHERE T3.C > 0
which is equivalent to the query:
SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3 WHERE T3.C > 0 AND T3.B=T2.B
Now the remaining outer join operation can be replaced by an
inner join, too, because the condition
T3.B=T2.B is null-rejected and we get a
query without outer joins at all:
SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3 WHERE T3.C > 0 AND T3.B=T2.B
Sometimes we succeed in replacing an embedded outer join operation, but cannot convert the embedding outer join. The following query:
SELECT * FROM T1 LEFT JOIN
(T2 LEFT JOIN T3 ON T3.B=T2.B)
ON T2.A=T1.A
WHERE T3.C > 0
is converted to:
SELECT * FROM T1 LEFT JOIN
(T2 INNER JOIN T3 ON T3.B=T2.B)
ON T2.A=T1.A
WHERE T3.C > 0,
That can be rewritten only to the form still containing the embedding outer join operation:
SELECT * FROM T1 LEFT JOIN
(T2,T3)
ON (T2.A=T1.A AND T3.B=T2.B)
WHERE T3.C > 0.
When trying to convert an embedded outer join operation in a
query, we must take into account the join condition for the
embedding outer join together with the
WHERE condition. In the query:
SELECT * FROM T1 LEFT JOIN
(T2 LEFT JOIN T3 ON T3.B=T2.B)
ON T2.A=T1.A AND T3.C=T1.C
WHERE T3.D > 0 OR T1.D > 0
the WHERE condition is not null-rejected
for the embedded outer join, but the join condition of the
embedding outer join T2.A=T1.A AND
T3.C=T1.C is null-rejected. So the query can be
converted to:
SELECT * FROM T1 LEFT JOIN
(T2, T3)
ON T2.A=T1.A AND T3.C=T1.C AND T3.B=T2.B
WHERE T3.D > 0 OR T1.D > 0
In some cases, MySQL can use an index to satisfy an
ORDER BY clause without doing extra
sorting.
The index can also be used even if the ORDER
BY does not match the index exactly, as long as all
unused portions of the index and all extra ORDER
BY columns are constants in the
WHERE clause. The following queries use the
index to resolve the ORDER BY part:
SELECT * FROM t1 ORDER BYkey_part1,key_part2,... ; SELECT * FROM t1 WHEREkey_part1=constantORDER BYkey_part2; SELECT * FROM t1 ORDER BYkey_part1DESC,key_part2DESC; SELECT * FROM t1 WHEREkey_part1= 1 ORDER BYkey_part1DESC,key_part2DESC; SELECT * FROM t1 WHEREkey_part1>constantORDER BYkey_part1ASC; SELECT * FROM t1 WHEREkey_part1<constantORDER BYkey_part1DESC; SELECT * FROM t1 WHEREkey_part1=constant1ANDkey_part2>constant2ORDER BYkey_part2;
In some cases, MySQL cannot use indexes
to resolve the ORDER BY, although it still
uses indexes to find the rows that match the
WHERE clause. These cases include the
following:
The query uses ORDER BY on different
indexes:
SELECT * FROM t1 ORDER BYkey1,key2;
The query uses ORDER BY on
nonconsecutive parts of an index:
SELECT * FROM t1 WHEREkey2=constantORDER BYkey_part2;
The query mixes ASC and
DESC:
SELECT * FROM t1 ORDER BYkey_part1DESC,key_part2ASC;
The index used to fetch the rows differs from the one used
in the ORDER BY:
SELECT * FROM t1 WHEREkey2=constantORDER BYkey1;
The query uses ORDER BY with an
expression that includes terms other than the index column
name:
SELECT * FROM t1 ORDER BY ABS(key); SELECT * FROM t1 ORDER BY -key;
The query joins many tables, and the columns in the
ORDER BY are not all from the first
nonconstant table that is used to retrieve rows. (This is
the first table in the
EXPLAIN output that does
not have a const join
type.)
The query has different ORDER BY and
GROUP BY expressions.
There is an index on only a prefix of a column named in
the ORDER BY clause. In this case, the
index cannot be used to fully resolve the sort order. For
example, if only the first 10 bytes of a
CHAR(20) column are
indexed, the index cannot distinguish values past the 10th
byte and a filesort will be needed.
The index does not store rows in order. For example, this
is true for a HASH index in a
MEMORY table.
Availability of an index for sorting may be affected by the
use of column aliases. Suppose that the column
t1.a is indexed. In this statement, the
name of the column in the select list is a.
It refers to t1.a, so for the reference to
a in the ORDER BY, the
index can be used:
SELECT a FROM t1 ORDER BY a;
In this statement, the name of the column in the select list
is also a, but it is the alias name. It
refers to ABS(a), so for the reference to
a in the ORDER BY, the
index cannot be used:
SELECT ABS(a) AS a FROM t1 ORDER BY a;
In the following statement, the ORDER BY
refers to a name that is not the name of a column in the
select list. But there is a column in t1
named a, so the ORDER BY
uses that and the index can be used. (The resulting sort order
may be completely different from the order for
ABS(a), of course.)
SELECT ABS(a) AS b FROM t1 ORDER BY a;
By default, MySQL sorts all GROUP BY
queries as if
you specified col1,
col2, ...ORDER BY
in the query as
well. If you include an explicit col1,
col2, ...ORDER BY
clause that contains the same column list, MySQL optimizes it
away without any speed penalty, although the sorting still
occurs.
Relying on implicit GROUP BY sorting is
deprecated. To achieve a specific sort order of grouped
results, it is preferable to use an explicit ORDER
BY clause. GROUP BY sorting is
a MySQL extension that may change in a future release; for
example, to make it possible for the optimizer to order
groupings in whatever manner it deems most efficient and to
avoid the sorting overhead.
If a query includes GROUP BY but you want
to avoid the overhead of sorting the result, you can suppress
sorting by specifying ORDER BY NULL. For
example:
INSERT INTO foo SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
The optimizer may still choose to use sorting to implement
grouping operations. ORDER BY NULL
suppresses sorting of the result, not prior sorting done by
grouping operations to determine the result.
With EXPLAIN SELECT
... ORDER BY, you can check whether MySQL can use
indexes to resolve the query. It cannot if you see
Using filesort in the
Extra column. See
Section 8.8.1, “Optimizing Queries with EXPLAIN”. Filesort uses a fixed-length
row-storage format similar to that used by the
MEMORY storage engine.
Variable-length types such as
VARCHAR are stored using a
fixed length.
MySQL has two filesort algorithms for
sorting and retrieving results. The original method uses only
the ORDER BY columns. The modified method
uses not just the ORDER BY columns, but all
the columns referenced by the query.
The optimizer selects which filesort
algorithm to use. It normally uses the modified algorithm
except when BLOB or
TEXT columns are involved, in
which case it uses the original algorithm. For both
algorithms, the sort buffer size is the
sort_buffer_size system
variable value.
The original filesort algorithm works as
follows:
Read all rows according to key or by table scanning. Skip
rows that do not match the WHERE
clause.
For each row, store in the sort buffer a tuple consisting of a pair of values (the sort key value and the row ID).
If all pairs fit into the sort buffer, no temporary file is created. Otherwise, when the sort buffer becomes full, run a qsort (quicksort) on it in memory and write it to a temporary file. Save a pointer to the sorted block.
Repeat the preceding steps until all rows have been read.
Do a multi-merge of up to MERGEBUFF (7)
regions to one block in another temporary file. Repeat
until all blocks from the first file are in the second
file.
Repeat the following until there are fewer than
MERGEBUFF2 (15) blocks left.
On the last multi-merge, only the row ID (the last part of the value pair) is written to a result file.
Read the rows in sorted order using the row IDs in the
result file. To optimize this, read in a large block of
row IDs, sort them, and use them to read the rows in
sorted order into a row buffer. The row buffer size is the
read_rnd_buffer_size
system variable value. The code for this step is in the
sql/records.cc source file.
One problem with this approach is that it reads rows twice:
One time during WHERE clause evaluation,
and again after sorting the value pairs. And even if the rows
were accessed successively the first time (for example, if a
table scan is done), the second time they are accessed
randomly. (The sort keys are ordered, but the row positions
are not.)
The modified filesort algorithm
incorporates an optimization to avoid reading the rows twice:
It records the sort key value, but instead of the row ID, it
records the columns referenced by the query. The modified
filesort algorithm works like this:
Read the rows that match the WHERE
clause.
For each row, store in the sort buffer a tuple consisting of the sort key value and the columns referenced by the query.
When the sort buffer becomes full, sort the tuples by sort key value in memory and write it to a temporary file.
After merge-sorting the temporary file, retrieve the rows in sorted order, but read the columns required by the query directly from the sorted tuples rather than by accessing the table a second time.
The tuples used by the modified filesort
algorithm are longer than the pairs used by the original
algorithm, and fewer of them fit in the sort buffer. As a
result, it is possible for the extra I/O to make the modified
approach slower, not faster. To avoid a slowdown, the
optimizer uses the modified algorithm only if the total size
of the extra columns in the sort tuple does not exceed the
value of the
max_length_for_sort_data
system variable. (A symptom of setting the value of this
variable too high is a combination of high disk activity and
low CPU activity.)
If a filesort is done,
EXPLAIN output includes
Using filesort in the
Extra column.
Suppose that a table t1 has four
VARCHAR columns a,
b, c, and
d and that the optimizer uses
filesort for this query:
SELECT * FROM t1 ORDER BY a, b;
The query sorts by a and
b, but returns all columns, so the columns
referenced by the query are a,
b, c, and
d. Depending on which
filesort algorithm the optimizer chooses,
the query executes as follows:
For the original algorithm, sort buffer tuples have these contents:
(fixed size a value, fixed size b value, row ID into t1)
The optimizer sorts on the fixed size values. After sorting,
the optimizer reads the tuples in order and uses the row ID in
each tuple to read rows from t1 to obtain
the select list column values.
For the modified algorithm, sort buffer tuples have these contents:
(fixed size a value, fixed size b value, a value, b value, c value, d value)
The optimizer sorts on the fixed size values. After sorting,
the optimizer reads the tuples in order and uses the values
for a, b,
c, and d to obtain the
select list column values without reading
t1 again.
For slow queries for which filesort is not
used, try lowering
max_length_for_sort_data to a
value that is appropriate to trigger a
filesort.
To increase ORDER BY speed, check whether
you can get MySQL to use indexes rather than an extra sorting
phase. If this is not possible, you can try the following
strategies:
Increase the
sort_buffer_size variable
value. Ideally, the value should be large enough for the
entire result set to fit in the sort buffer (to avoid
writes to disk and merge passes), but at minimum the value
must be large enough to accommodate fifteen tuples.
Take into account that the size of column values stored in
the sort buffer is affected by the
max_sort_length system
variable value. For example, if tuples store values of
long string columns and you increase the value of
max_sort_length, the size
of sort buffer tuples increases as well and may require
you to increase
sort_buffer_size. For
column values calculated as a result of string expressions
(such as those that invoke a string-valued function), the
filesort algorithm cannot tell the
maximum length of expression values, so it must allocate
max_sort_length bytes for
each tuple.
To monitor the number of merge passes, check the
Sort_merge_passes status
variable.
Increase the
read_rnd_buffer_size
variable value.
Use less RAM per row by declaring columns only as large as
they need to be to hold the values stored in them. For
example, CHAR(16) is better than
CHAR(200) if values never exceed 16
characters.
Change the tmpdir system
variable to point to a dedicated file system with large
amounts of free space. The variable value can list several
paths that are used in round-robin fashion; you can use
this feature to spread the load across several
directories. Paths should be separated by colon characters
(“:”) on Unix and
semicolon characters (“;”)
on Windows. The paths should name directories in file
systems located on different physical
disks, not different partitions on the same disk.
The most general way to satisfy a GROUP BY
clause is to scan the whole table and create a new temporary
table where all rows from each group are consecutive, and then
use this temporary table to discover groups and apply
aggregate functions (if any). In some cases, MySQL is able to
do much better than that and to avoid creation of temporary
tables by using index access.
The most important preconditions for using indexes for
GROUP BY are that all GROUP
BY columns reference attributes from the same index,
and that the index stores its keys in order (for example, this
is a BTREE index and not a
HASH index). Whether use of temporary
tables can be replaced by index access also depends on which
parts of an index are used in a query, the conditions
specified for these parts, and the selected aggregate
functions.
There are two ways to execute a GROUP BY
query through index access, as detailed in the following
sections. In the first method, the grouping operation is
applied together with all range predicates (if any). The
second method first performs a range scan, and then groups the
resulting tuples.
In MySQL, GROUP BY is used for sorting, so
the server may also apply ORDER BY
optimizations to grouping. However, relying on implicit
GROUP BY sorting is deprecated. See
Section 8.2.1.11, “ORDER BY Optimization”.
The most efficient way to process GROUP
BY is when an index is used to directly retrieve
the grouping columns. With this access method, MySQL uses
the property of some index types that the keys are ordered
(for example, BTREE). This property
enables use of lookup groups in an index without having to
consider all keys in the index that satisfy all
WHERE conditions. This access method
considers only a fraction of the keys in an index, so it is
called a loose index
scan. When there is no WHERE
clause, a loose index scan reads as many keys as the number
of groups, which may be a much smaller number than that of
all keys. If the WHERE clause contains
range predicates (see the discussion of the
range join type in
Section 8.8.1, “Optimizing Queries with EXPLAIN”), a loose index scan looks
up the first key of each group that satisfies the range
conditions, and again reads the least possible number of
keys. This is possible under the following conditions:
The query is over a single table.
The GROUP BY names only columns that
form a leftmost prefix of the index and no other
columns. (If, instead of GROUP BY,
the query has a DISTINCT clause, all
distinct attributes refer to columns that form a
leftmost prefix of the index.) For example, if a table
t1 has an index on
(c1,c2,c3), loose index scan is
applicable if the query has GROUP BY c1,
c2,. It is not applicable if the query has
GROUP BY c2, c3 (the columns are not
a leftmost prefix) or GROUP BY c1, c2,
c4 (c4 is not in the
index).
The only aggregate functions used in the select list (if
any) are MIN() and
MAX(), and all of them
refer to the same column. The column must be in the
index and must immediately follow the columns in the
GROUP BY.
Any other parts of the index than those from the
GROUP BY referenced in the query must
be constants (that is, they must be referenced in
equalities with constants), except for the argument of
MIN() or
MAX() functions.
For columns in the index, full column values must be
indexed, not just a prefix. For example, with
c1 VARCHAR(20), INDEX (c1(10)), the
index cannot be used for loose index scan.
If loose index scan is applicable to a query, the
EXPLAIN output shows
Using index for group-by in the
Extra column.
Assume that there is an index
idx(c1,c2,c3) on table
t1(c1,c2,c3,c4). The loose index scan
access method can be used for the following queries:
SELECT c1, c2 FROM t1 GROUP BY c1, c2; SELECT DISTINCT c1, c2 FROM t1; SELECT c1, MIN(c2) FROM t1 GROUP BY c1; SELECT c1, c2 FROM t1 WHERE c1 <constGROUP BY c1, c2; SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 >constGROUP BY c1, c2; SELECT c2 FROM t1 WHERE c1 <constGROUP BY c1, c2; SELECT c1, c2 FROM t1 WHERE c3 =constGROUP BY c1, c2;
The following queries cannot be executed with this quick select method, for the reasons given:
There are aggregate functions other than
MIN() or
MAX():
SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
The columns in the GROUP BY clause do
not form a leftmost prefix of the index:
SELECT c1, c2 FROM t1 GROUP BY c2, c3;
The query refers to a part of a key that comes after the
GROUP BY part, and for which there is
no equality with a constant:
SELECT c1, c3 FROM t1 GROUP BY c1, c2;
Were the query to include WHERE c3 =
, loose index
scan could be used.
const
As of MySQL 5.5, the loose index scan access method can be
applied to other forms of aggregate function references in
the select list, in addition to the
MIN() and
MAX() references already
supported:
AVG(DISTINCT),
SUM(DISTINCT), and
COUNT(DISTINCT) are
supported. AVG(DISTINCT)
and SUM(DISTINCT) take a
single argument.
COUNT(DISTINCT) can have
more than one column argument.
There must be no GROUP BY or
DISTINCT clause in the query.
The loose scan limitations described earlier still apply.
Assume that there is an index
idx(c1,c2,c3) on table
t1(c1,c2,c3,c4). The loose index scan
access method can be used for the following queries:
SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1; SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;
Loose index scan is not applicable for the following queries:
SELECT DISTINCT COUNT(DISTINCT c1) FROM t1; SELECT COUNT(DISTINCT c1) FROM t1 GROUP BY c1;
A tight index scan may be either a full index scan or a range index scan, depending on the query conditions.
When the conditions for a loose index scan are not met, it
still may be possible to avoid creation of temporary tables
for GROUP BY queries. If there are range
conditions in the WHERE clause, this
method reads only the keys that satisfy these conditions.
Otherwise, it performs an index scan. Because this method
reads all keys in each range defined by the
WHERE clause, or scans the whole index if
there are no range conditions, we term it a
tight index scan. With
a tight index scan, the grouping operation is performed only
after all keys that satisfy the range conditions have been
found.
For this method to work, it is sufficient that there is a
constant equality condition for all columns in a query
referring to parts of the key coming before or in between
parts of the GROUP BY key. The constants
from the equality conditions fill in any “gaps”
in the search keys so that it is possible to form complete
prefixes of the index. These index prefixes then can be used
for index lookups. If we require sorting of the
GROUP BY result, and it is possible to
form search keys that are prefixes of the index, MySQL also
avoids extra sorting operations because searching with
prefixes in an ordered index already retrieves all the keys
in order.
Assume that there is an index
idx(c1,c2,c3) on table
t1(c1,c2,c3,c4). The following queries do
not work with the loose index scan access method described
earlier, but still work with the tight index scan access
method.
There is a gap in the GROUP BY, but
it is covered by the condition c2 =
'a':
SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
The GROUP BY does not begin with the
first part of the key, but there is a condition that
provides a constant for that part:
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
DISTINCT combined with ORDER
BY needs a temporary table in many cases.
Because DISTINCT may use GROUP
BY, learn how MySQL works with columns in
ORDER BY or HAVING
clauses that are not part of the selected columns. See
Section 12.17.3, “MySQL Handling of GROUP BY”.
In most cases, a DISTINCT clause can be
considered as a special case of GROUP BY.
For example, the following two queries are equivalent:
SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 >const; SELECT c1, c2, c3 FROM t1 WHERE c1 >constGROUP BY c1, c2, c3;
Due to this equivalence, the optimizations applicable to
GROUP BY queries can be also applied to
queries with a DISTINCT clause. Thus, for
more details on the optimization possibilities for
DISTINCT queries, see
Section 8.2.1.12, “GROUP BY Optimization”.
When combining LIMIT
with
row_countDISTINCT, MySQL stops as soon as it finds
row_count unique rows.
If you do not use columns from all tables named in a query,
MySQL stops scanning any unused tables as soon as it finds the
first match. In the following case, assuming that
t1 is used before t2
(which you can check with
EXPLAIN), MySQL stops reading
from t2 (for any particular row in
t1) when it finds the first row in
t2:
SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;
Certain optimizations are applicable to comparisons that use
the IN operator to test subquery results
(or that use =ANY, which is equivalent).
This section discusses these optimizations, particularly with
regard to the challenges that NULL values
present. The last part of the discussion includes suggestions
on what you can do to help the optimizer.
Consider the following subquery comparison:
outer_exprIN (SELECTinner_exprFROM ... WHEREsubquery_where)
MySQL evaluates queries “from outside to inside.”
That is, it first obtains the value of the outer expression
outer_expr, and then runs the
subquery and captures the rows that it produces.
A very useful optimization is to “inform” the
subquery that the only rows of interest are those where the
inner expression inner_expr is
equal to outer_expr. This is done
by pushing down an appropriate equality into the subquery's
WHERE clause. That is, the comparison is
converted to this:
EXISTS (SELECT 1 FROM ... WHEREsubquery_whereANDouter_expr=inner_expr)
After the conversion, MySQL can use the pushed-down equality to limit the number of rows that it must examine when evaluating the subquery.
More generally, a comparison of N
values to a subquery that returns
N-value rows is subject to the same
conversion. If oe_i and
ie_i represent corresponding outer
and inner expression values, this subquery comparison:
(oe_1, ...,oe_N) IN (SELECTie_1, ...,ie_NFROM ... WHEREsubquery_where)
Becomes:
EXISTS (SELECT 1 FROM ... WHEREsubquery_whereANDoe_1=ie_1AND ... ANDoe_N=ie_N)
The following discussion assumes a single pair of outer and inner expression values for simplicity.
The conversion just described has its limitations. It is valid
only if we ignore possible NULL values.
That is, the “pushdown” strategy works as long as
both of these two conditions are true:
When either or both of those conditions do not hold, optimization is more complex.
Suppose that outer_expr is known to
be a non-NULL value but the subquery does
not produce a row such that
outer_expr =
inner_expr. Then
outer_expr IN (SELECT
...)
In this situation, the approach of looking for rows with
outer_expr =
inner_exprinner_expr is
NULL. Roughly speaking, the subquery can be
converted to:
EXISTS (SELECT 1 FROM ... WHEREsubquery_whereAND (outer_expr=inner_exprORinner_exprIS NULL))
The need to evaluate the extra IS
NULL condition is why MySQL has the
ref_or_null access method:
mysql>EXPLAIN->SELECT->outer_exprIN (SELECT t2.maybe_null_keyFROM t2, t3 WHERE ...)-> FROM t1; *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 ... *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: t2 type: ref_or_null possible_keys: maybe_null_key key: maybe_null_key key_len: 5 ref: func rows: 2 Extra: Using where; Using index ...
The unique_subquery and
index_subquery
subquery-specific access methods also have “or
NULL” variants. However, they are
not visible in EXPLAIN output,
so you must use EXPLAIN
EXTENDED followed by SHOW
WARNINGS (note the checking NULL
in the warning message):
mysql>EXPLAIN EXTENDED->SELECT*************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 ... *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: t2 type: index_subquery possible_keys: maybe_null_key key: maybe_null_key key_len: 5 ref: func rows: 2 Extra: Using index mysql>outer_exprIN (SELECT maybe_null_key FROM t2) FROM t1\GSHOW WARNINGS\G*************************** 1. row *************************** Level: Note Code: 1003 Message: select (`test`.`t1`.`outer_expr`, (((`test`.`t1`.`outer_expr`) in t2 on maybe_null_key checking NULL))) AS `outer_expr IN (SELECT maybe_null_key FROM t2)` from `test`.`t1`
The additional OR ... IS NULL condition
makes query execution slightly more complicated (and some
optimizations within the subquery become inapplicable), but
generally this is tolerable.
The situation is much worse when
outer_expr can be
NULL. According to the SQL interpretation
of NULL as “unknown value,”
NULL IN (SELECT should evaluate to:
inner_expr
...)
For proper evaluation, it is necessary to be able to check
whether the SELECT has produced
any rows at all, so
outer_expr =
inner_expr
Essentially, there must be different ways to execute the
subquery depending on the value of
outer_expr.
The optimizer chooses SQL compliance over speed, so it
accounts for the possibility that
outer_expr might be
NULL.
If outer_expr is
NULL, to evaluate the following expression,
it is necessary to run the
SELECT to determine whether it
produces any rows:
NULL IN (SELECTinner_exprFROM ... WHEREsubquery_where)
It is necessary to run the original
SELECT here, without any
pushed-down equalities of the kind mentioned earlier.
On the other hand, when outer_expr
is not NULL, it is absolutely essential
that this comparison:
outer_exprIN (SELECTinner_exprFROM ... WHEREsubquery_where)
be converted to this expression that uses a pushed-down condition:
EXISTS (SELECT 1 FROM ... WHEREsubquery_whereANDouter_expr=inner_expr)
Without this conversion, subqueries will be slow. To solve the dilemma of whether to push down or not push down conditions into the subquery, the conditions are wrapped in “trigger” functions. Thus, an expression of the following form:
outer_exprIN (SELECTinner_exprFROM ... WHEREsubquery_where)
is converted into:
EXISTS (SELECT 1 FROM ... WHEREsubquery_whereAND trigcond(outer_expr=inner_expr))
More generally, if the subquery comparison is based on several pairs of outer and inner expressions, the conversion takes this comparison:
(oe_1, ...,oe_N) IN (SELECTie_1, ...,ie_NFROM ... WHEREsubquery_where)
and converts it to this expression:
EXISTS (SELECT 1 FROM ... WHEREsubquery_whereAND trigcond(oe_1=ie_1) AND ... AND trigcond(oe_N=ie_N) )
Each trigcond(
is a special function that evaluates to the following values:
X)
X when the
“linked” outer expression
oe_i is not
NULL
TRUE when the “linked”
outer expression oe_i is
NULL
Trigger functions are not triggers of
the kind that you create with CREATE
TRIGGER.
Equalities that are wrapped into trigcond()
functions are not first class predicates for the query
optimizer. Most optimizations cannot deal with predicates that
may be turned on and off at query execution time, so they
assume any
trigcond( to
be an unknown function and ignore it. At the moment, triggered
equalities can be used by those optimizations:
X)
Reference optimizations:
trigcond( can
be used to construct
X=Y
[OR Y IS NULL])ref,
eq_ref, or
ref_or_null table
accesses.
Index lookup-based subquery execution engines:
trigcond(
can be used to construct
X=Y)unique_subquery or
index_subquery
accesses.
Table-condition generator: If the subquery is a join of several tables, the triggered condition will be checked as soon as possible.
When the optimizer uses a triggered condition to create some
kind of index lookup-based access (as for the first two items
of the preceding list), it must have a fallback strategy for
the case when the condition is turned off. This fallback
strategy is always the same: Do a full table scan. In
EXPLAIN output, the fallback
shows up as Full scan on NULL key in the
Extra column:
mysql>EXPLAIN SELECT t1.col1,->t1.col1 IN (SELECT t2.key1 FROM t2 WHERE t2.col2=t1.col2) FROM t1\G*************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 ... *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: t2 type: index_subquery possible_keys: key1 key: key1 key_len: 5 ref: func rows: 2 Extra: Using where; Full scan on NULL key
If you run EXPLAIN EXTENDED
followed by SHOW WARNINGS, you
can see the triggered condition:
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`col1` AS `col1`,
<in_optimizer>(`test`.`t1`.`col1`,
<exists>(<index_lookup>(<cache>(`test`.`t1`.`col1`) in t2
on key1 checking NULL
where (`test`.`t2`.`col2` = `test`.`t1`.`col2`) having
trigcond(<is_not_null_test>(`test`.`t2`.`key1`))))) AS
`t1.col1 IN (select t2.key1 from t2 where t2.col2=t1.col2)`
from `test`.`t1`
The use of triggered conditions has some performance
implications. A NULL IN (SELECT ...)
expression now may cause a full table scan (which is slow)
when it previously did not. This is the price paid for correct
results (the goal of the trigger-condition strategy was to
improve compliance and not speed).
For multiple-table subqueries, execution of NULL IN
(SELECT ...) will be particularly slow because the
join optimizer does not optimize for the case where the outer
expression is NULL. It assumes that
subquery evaluations with NULL on the left
side are very rare, even if there are statistics that indicate
otherwise. On the other hand, if the outer expression might be
NULL but never actually is, there is no
performance penalty.
To help the query optimizer better execute your queries, use these tips:
Declare a column as NOT NULL if it
really is. (This also helps other aspects of the optimizer
by simplifying condition testing for the column.)
If you do not need to distinguish a
NULL from FALSE
subquery result, you can easily avoid the slow execution
path. Replace a comparison that looks like this:
outer_exprIN (SELECTinner_exprFROM ...)
with this expression:
(outer_exprIS NOT NULL) AND (outer_exprIN (SELECTinner_exprFROM ...))
Then NULL IN (SELECT ...) will never be
evaluated because MySQL stops evaluating
AND parts as soon as the
expression result is clear.
If you need only a specified number of rows from a result set,
use a LIMIT clause in the query, rather
than fetching the whole result set and throwing away the extra
data.
MySQL sometimes optimizes a query that has a LIMIT
clause and no
row_countHAVING clause:
If you select only a few rows with
LIMIT, MySQL uses indexes in some cases
when normally it would prefer to do a full table scan.
If you combine LIMIT
with
row_countORDER BY, MySQL ends the sorting as
soon as it has found the first
row_count rows of the sorted
result, rather than sorting the entire result. If ordering
is done by using an index, this is very fast. If a
filesort must be done, all rows that match the query
without the LIMIT clause are selected,
and most or all of them are sorted, before the first
row_count are found. After the
initial rows have been found, MySQL does not sort any
remainder of the result set.
One manifestation of this behavior is that an
ORDER BY query with and without
LIMIT may return rows in different
order, as described later in this section.
If you combine LIMIT
with
row_countDISTINCT, MySQL stops as soon as it
finds row_count unique rows.
In some cases, a GROUP BY can be
resolved by reading the index in order (or doing a sort on
the index) and then calculating summaries until the index
value changes. In this case, LIMIT
does not
calculate any unnecessary row_countGROUP BY
values.
As soon as MySQL has sent the required number of rows to
the client, it aborts the query unless you are using
SQL_CALC_FOUND_ROWS. The number of rows
can then be retrieved with SELECT
FOUND_ROWS(). See
Section 12.14, “Information Functions”.
LIMIT 0 quickly returns an empty set.
This can be useful for checking the validity of a query.
It can also be employed to obtain the types of the result
columns if you are using a MySQL API that makes result set
metadata available. With the mysql
client program, you can use the
--column-type-info option to
display result column types.
If the server uses temporary tables to resolve the query,
it uses the LIMIT
clause to
calculate how much space is required.
row_count
If multiple rows have identical values in the ORDER
BY columns, the server is free to return those rows
in any order, and may do so differently depending on the
overall execution plan. In other words, the sort order of
those rows is nondeterministic with respect to the nonordered
columns.
One factor that affects the execution plan is
LIMIT, so an ORDER BY
query with and without LIMIT may return
rows in different orders. Consider this query, which is sorted
by the category column but nondeterministic
with respect to the id and
rating columns:
mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 3 | 2 | 3.7 |
| 4 | 2 | 3.5 |
| 6 | 2 | 3.5 |
| 2 | 3 | 5.0 |
| 7 | 3 | 2.7 |
+----+----------+--------+
Including LIMIT may affect order of rows
within each category value. For example,
this is a valid query result:
mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 4 | 2 | 3.5 |
| 3 | 2 | 3.7 |
| 6 | 2 | 3.5 |
+----+----------+--------+
In each case, the rows are sorted by the ORDER
BY column, which is all that is required by the SQL
standard.
If it is important to ensure the same row order with and
without LIMIT, include additional columns
in the ORDER BY clause to make the order
deterministic. For example, if id values
are unique, you can make rows for a given
category value appear in
id order by sorting like this:
mysql>SELECT * FROM ratings ORDER BY category, id;+----+----------+--------+ | id | category | rating | +----+----------+--------+ | 1 | 1 | 4.5 | | 5 | 1 | 3.2 | | 3 | 2 | 3.7 | | 4 | 2 | 3.5 | | 6 | 2 | 3.5 | | 2 | 3 | 5.0 | | 7 | 3 | 2.7 | +----+----------+--------+ mysql>SELECT * FROM ratings ORDER BY category, id LIMIT 5;+----+----------+--------+ | id | category | rating | +----+----------+--------+ | 1 | 1 | 4.5 | | 5 | 1 | 3.2 | | 3 | 2 | 3.7 | | 4 | 2 | 3.5 | | 6 | 2 | 3.5 | +----+----------+--------+
Row constructors permit simultaneous comparisons of multiple values. For example, these two statements are semantically equivalent:
SELECT * FROM t1 WHERE (column1,column2) = (1,1); SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;
In addition, the optimizer handles both expressions the same way.
The optimizer is less likely to use available indexes if the
row constructor columns do not cover the prefix of an index.
Consider the following table, which has a primary key on
(c1, c2, c3):
CREATE TABLE t1 ( c1 INT, c2 INT, c3 INT, c4 CHAR(100), PRIMARY KEY(c1,c2,c3) );
In this query, the WHERE clause uses all
columns in the index. However, the row constructor itself does
not cover an index prefix, with the result that the optimizer
uses only c1 (key_len=4,
the size of c1):
mysql>EXPLAIN SELECT * FROM t1->WHERE c1=1 AND (c2,c3) > (1,1)\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 3 Extra: Using where
In such cases, rewriting the row constructor expression using an equivalent nonconstructor expression may result in more complete index use. For the given query, the row constructor and equivalent nonconstructor expressions are:
(c2,c3) > (1,1) c2 > 1 OR ((c2 = 1) AND (c3 > 1))
Rewriting the query to use the nonconstructor expression
results in the optimizer using all three columns in the index
(key_len=12):
mysql>EXPLAIN SELECT * FROM t1->WHERE c1 = 1 AND (c2 > 1 OR ((c2 = 1) AND (c3 > 1)))\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 12 ref: NULL rows: 3 Extra: Using where
Thus, for better results, avoid mixing row constructors with
AND/OR
expressions. Use one or the other.
Frequently, a full table
scan is a danger sign that a query can be speeded up
significantly. For tables with more than a few rows, consider
redesigning the query by adding an index for one or more of
the columns tested in the WHERE clause. Put
extra effort into avoiding table scans for queries that
perform joins or reference foreign keys. If the nature of the
data means there is no way to avoid reading all the rows, then
it might not be practical to make the query faster, or making
it faster might involve extensive restructuring of your tables
that is beyond the scope of this section.
The output from EXPLAIN shows
ALL in the
type column when MySQL uses a table scan to
resolve a query. This usually happens under the following
conditions:
The ON or WHERE
clauses do not reference any indexed columns that the
query can use. Consider adding an index, or refining those
clauses to refer to an indexed column.
The table is so small that it is faster to perform a table scan than to bother with a key lookup. This is common for tables with fewer than 10 rows and a short row length. Don't worry in this case.
You are comparing indexed columns with constant values and MySQL has calculated (based on the index tree) that the constants cover too large a part of the table and that a table scan would be faster. See Section 8.2.1.2, “How MySQL Optimizes WHERE Clauses”. For example, to query census data only for males or only for females, MySQL must read most of the data blocks in the table, so locating the rows through the index would add unnecessary overhead. Don't worry if you encounter this condition for occasional big reports. If these reports are frequent or truly time-critical, and the table is huge, you might partition, shard, or create dimension tables using the relevant column.
You are using a key with low cardinality (many rows match the key value) through another column. In this case, MySQL assumes that by using the key it probably will do many key lookups and that a table scan would be faster.
For small tables, a table scan often is appropriate and the performance impact is negligible. For large tables, try the following techniques to avoid having the optimizer incorrectly choose a table scan:
Minimize the OR keywords in your
WHERE clauses. If there is no index
that helps to locate the values on
both sides of the
OR, any row could potentially be part
of the result set, so all rows must be tested, and that
requires a full table scan. If you have one index that
helps to optimize one side of an OR
query, and a different index that helps to optimize the
other side, use a UNION operator to run
separate fast queries and merge the results afterward.
With tables that use the MEMORY storage
engine, if you run queries that examine ranges of values
(using operators such as >,
<=, or BETWEEN on
the indexed columns), create the index with the
USING BTREE clause. The default
(USING HASH) is fast for retrieving
individual rows with an equality operator
(= or <=>),
but is much slower (requiring a full table scan) to
examine a range of column values. A
MEMORY table created with the
USING BTREE clause is still fast for
equality comparisons, so use that clause for your
MEMORY tables that handle a variety of
queries.
Use ANALYZE TABLE
to update
the key distributions for the scanned table. See
Section 13.7.2.1, “ANALYZE TABLE Syntax”.
tbl_name
Use FORCE INDEX for the scanned table
to tell MySQL that table scans are very expensive compared
to using the given index:
SELECT * FROM t1, t2 FORCE INDEX (index_for_column) WHERE t1.col_name=t2.col_name;
Start mysqld with the
--max-seeks-for-key=1000
option or use SET
max_seeks_for_key=1000 to tell the optimizer to
assume that no key scan causes more than 1,000 key seeks.
See Section 5.1.4, “Server System Variables”.
This section explains how to speed up data change statements:
INSERT,
UPDATE, and
DELETE. Traditional OLTP
applications and modern web applications typically do many small
data change operations, where concurrency is vital. Data
analysis and reporting applications typically run data change
operations that affect many rows at once, where the main
considerations is the I/O to write large amounts of data and
keep indexes up-to-date. For inserting and updating large
volumes of data (known in the industry as ETL, for
“extract-transform-load”), sometimes you use other
SQL statements or external commands, that mimic the effects of
INSERT,
UPDATE, and
DELETE statements.
To optimize insert speed, combine many small operations into a single large operation. Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end.
The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:
Connecting: (3)
Sending query to server: (2)
Parsing query: (2)
Inserting row: (1 × size of row)
Inserting indexes: (1 × number of indexes)
Closing: (1)
This does not take into consideration the initial overhead to open tables, which is done once for each concurrently running query.
The size of the table slows down the insertion of indexes by
log N, assuming B-tree indexes.
You can use the following methods to speed up inserts:
If you are inserting many rows from the same client at the
same time, use INSERT
statements with multiple VALUES lists
to insert several rows at a time. This is considerably
faster (many times faster in some cases) than using
separate single-row INSERT
statements. If you are adding data to a nonempty table,
you can tune the
bulk_insert_buffer_size
variable to make data insertion even faster. See
Section 5.1.4, “Server System Variables”.
When loading a table from a text file, use
LOAD DATA
INFILE. This is usually 20 times faster than
using INSERT statements.
See Section 13.2.6, “LOAD DATA INFILE Syntax”.
Take advantage of the fact that columns have default values. Insert values explicitly only when the value to be inserted differs from the default. This reduces the parsing that MySQL must do and improves the insert speed.
See Section 8.5.4, “Bulk Data Loading for InnoDB Tables”
for tips specific to InnoDB tables.
See Section 8.6.2, “Bulk Data Loading for MyISAM Tables”
for tips specific to MyISAM tables.
An update statement is optimized like a
SELECT query with the
additional overhead of a write. The speed of the write depends
on the amount of data being updated and the number of indexes
that are updated. Indexes that are not changed do not get
updated.
Another way to get fast updates is to delay updates and then do many updates in a row later. Performing multiple updates together is much quicker than doing one at a time if you lock the table.
For a MyISAM table that uses dynamic row
format, updating a row to a longer total length may split the
row. If you do this often, it is very important to use
OPTIMIZE TABLE occasionally.
See Section 13.7.2.4, “OPTIMIZE TABLE Syntax”.
The time required to delete individual rows in a
MyISAM table is exactly proportional to the
number of indexes. To delete rows more quickly, you can
increase the size of the key cache by increasing the
key_buffer_size system
variable. See Section 8.12.2, “Tuning Server Parameters”.
To delete all rows from a MyISAM table,
TRUNCATE TABLE
is faster than
tbl_nameDELETE FROM
. Truncate
operations are not transaction-safe; an error occurs when
attempting one in the course of an active transaction or
active table lock. See Section 13.1.33, “TRUNCATE TABLE Syntax”.
tbl_name
The more complex your privilege setup, the more overhead applies
to all SQL statements. Simplifying the privileges established by
GRANT statements enables MySQL to
reduce permission-checking overhead when clients execute
statements. For example, if you do not grant any table-level or
column-level privileges, the server need not ever check the
contents of the tables_priv and
columns_priv tables. Similarly, if you place
no resource limits on any accounts, the server does not have to
perform resource counting. If you have a very high
statement-processing load, consider using a simplified grant
structure to reduce permission-checking overhead.
Applications that monitor the database can make frequent use of
the INFORMATION_SCHEMA tables. Certain types
of queries for INFORMATION_SCHEMA tables can
be optimized to execute more quickly. The goal is to minimize
file operations (for example, scanning a directory or opening a
table file) to collect the information that makes up these
dynamic tables. These optimizations do have an effect on how
collations are used for searches in
INFORMATION_SCHEMA tables. For more
information, see
Section 10.1.8.8, “Collation and INFORMATION_SCHEMA Searches”.
1) Try to use constant lookup values for
database and table names in the WHERE
clause
You can take advantage of this principle as follows:
To look up databases or tables, use expressions that evaluate to a constant, such as literal values, functions that return a constant, or scalar subqueries.
Avoid queries that use a nonconstant database name lookup value (or no lookup value) because they require a scan of the data directory to find matching database directory names.
Within a database, avoid queries that use a nonconstant table name lookup value (or no lookup value) because they require a scan of the database directory to find matching table files.
This principle applies to the
INFORMATION_SCHEMA tables shown in the
following table, which shows the columns for which a constant
lookup value enables the server to avoid a directory scan. For
example, if you are selecting from
TABLES, using a constant lookup
value for TABLE_SCHEMA in the
WHERE clause enables a data directory scan to
be avoided.
| Table | Column to specify to avoid data directory scan | Column to specify to avoid database directory scan |
|---|---|---|
COLUMNS | TABLE_SCHEMA | TABLE_NAME |
KEY_COLUMN_USAGE | TABLE_SCHEMA | TABLE_NAME |
PARTITIONS | TABLE_SCHEMA | TABLE_NAME |
REFERENTIAL_CONSTRAINTS | CONSTRAINT_SCHEMA | TABLE_NAME |
STATISTICS | TABLE_SCHEMA | TABLE_NAME |
TABLES | TABLE_SCHEMA | TABLE_NAME |
TABLE_CONSTRAINTS | TABLE_SCHEMA | TABLE_NAME |
TRIGGERS | EVENT_OBJECT_SCHEMA | EVENT_OBJECT_TABLE |
VIEWS | TABLE_SCHEMA | TABLE_NAME |
The benefit of a query that is limited to a specific constant database name is that checks need be made only for the named database directory. Example:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test';
Use of the literal database name test enables
the server to check only the test database
directory, regardless of how many databases there might be. By
contrast, the following query is less efficient because it
requires a scan of the data directory to determine which
database names match the pattern 'test%':
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'test%';
For a query that is limited to a specific constant table name, checks need be made only for the named table within the corresponding database directory. Example:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
Use of the literal table name t1 enables the
server to check only the files for the t1
table, regardless of how many tables there might be in the
test database. By contrast, the following
query requires a scan of the test database
directory to determine which table names match the pattern
't%':
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't%';
The following query requires a scan of the database directory to
determine matching database names for the pattern
'test%', and for each matching database, it
requires a scan of the database directory to determine matching
table names for the pattern 't%':
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test%' AND TABLE_NAME LIKE 't%';
2) Write queries that minimize the number of table files that must be opened
For queries that refer to certain
INFORMATION_SCHEMA table columns, several
optimizations are available that minimize the number of table
files that must be opened. Example:
SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test';
In this case, after the server has scanned the database
directory to determine the names of the tables in the database,
those names become available with no further file system
lookups. Thus, TABLE_NAME requires no files
to be opened. The ENGINE (storage engine)
value can be determined by opening the table's
.frm file, without touching other table
files such as the .MYD or
.MYI file.
Some values, such as INDEX_LENGTH for
MyISAM tables, require opening the
.MYD or .MYI file as
well.
The file-opening optimization types are denoted thus:
SKIP_OPEN_TABLE: Table files do not need
to be opened. The information has already become available
within the query by scanning the database directory.
OPEN_FRM_ONLY: Only the table's
.frm file need be opened.
OPEN_TRIGGER_ONLY: Only the table's
.TRG file need be opened.
OPEN_FULL_TABLE: The unoptimized
information lookup. The .frm,
.MYD, and .MYI
files must be opened.
The following list indicates how the preceding optimization
types apply to INFORMATION_SCHEMA table
columns. For tables and columns not named, none of the
optimizations apply.
COLUMNS:
OPEN_FRM_ONLY applies to all columns
KEY_COLUMN_USAGE:
OPEN_FULL_TABLE applies to all columns
PARTITIONS:
OPEN_FULL_TABLE applies to all columns
REFERENTIAL_CONSTRAINTS:
OPEN_FULL_TABLE applies to all columns
| Column | Optimization type |
|---|---|
TABLE_CATALOG | OPEN_FRM_ONLY |
TABLE_SCHEMA | OPEN_FRM_ONLY |
TABLE_NAME | OPEN_FRM_ONLY |
NON_UNIQUE | OPEN_FRM_ONLY |
INDEX_SCHEMA | OPEN_FRM_ONLY |
INDEX_NAME | OPEN_FRM_ONLY |
SEQ_IN_INDEX | OPEN_FRM_ONLY |
COLUMN_NAME | OPEN_FRM_ONLY |
COLLATION | OPEN_FRM_ONLY |
CARDINALITY | OPEN_FULL_TABLE |
SUB_PART | OPEN_FRM_ONLY |
PACKED | OPEN_FRM_ONLY |
NULLABLE | OPEN_FRM_ONLY |
INDEX_TYPE | OPEN_FULL_TABLE |
COMMENT | OPEN_FRM_ONLY |
| Column | Optimization type |
|---|---|
TABLE_CATALOG | SKIP_OPEN_TABLE |
TABLE_SCHEMA | SKIP_OPEN_TABLE |
TABLE_NAME | SKIP_OPEN_TABLE |
TABLE_TYPE | OPEN_FRM_ONLY |
ENGINE | OPEN_FRM_ONLY |
VERSION | OPEN_FRM_ONLY |
ROW_FORMAT | OPEN_FULL_TABLE |
TABLE_ROWS | OPEN_FULL_TABLE |
AVG_ROW_LENGTH | OPEN_FULL_TABLE |
DATA_LENGTH | OPEN_FULL_TABLE |
MAX_DATA_LENGTH | OPEN_FULL_TABLE |
INDEX_LENGTH | OPEN_FULL_TABLE |
DATA_FREE | OPEN_FULL_TABLE |
AUTO_INCREMENT | OPEN_FULL_TABLE |
CREATE_TIME | OPEN_FULL_TABLE |
UPDATE_TIME | OPEN_FULL_TABLE |
CHECK_TIME | OPEN_FULL_TABLE |
TABLE_COLLATION | OPEN_FRM_ONLY |
CHECKSUM | OPEN_FULL_TABLE |
CREATE_OPTIONS | OPEN_FRM_ONLY |
TABLE_COMMENT | OPEN_FRM_ONLY |
TABLE_CONSTRAINTS:
OPEN_FULL_TABLE applies to all columns
TRIGGERS:
OPEN_TRIGGER_ONLY applies to all columns
| Column | Optimization type |
|---|---|
TABLE_CATALOG | OPEN_FRM_ONLY |
TABLE_SCHEMA | OPEN_FRM_ONLY |
TABLE_NAME | OPEN_FRM_ONLY |
VIEW_DEFINITION | OPEN_FRM_ONLY |
CHECK_OPTION | OPEN_FRM_ONLY |
IS_UPDATABLE | OPEN_FULL_TABLE |
DEFINER | OPEN_FRM_ONLY |
SECURITY_TYPE | OPEN_FRM_ONLY |
CHARACTER_SET_CLIENT | OPEN_FRM_ONLY |
COLLATION_CONNECTION | OPEN_FRM_ONLY |
3) Use
EXPLAIN to determine whether the
server can use INFORMATION_SCHEMA
optimizations for a query
This applies particularly for
INFORMATION_SCHEMA queries that search for
information from more than one database, which might take a long
time and impact performance. The Extra value
in EXPLAIN output indicates
which, if any, of the optimizations described earlier the server
can use to evaluate INFORMATION_SCHEMA
queries. The following examples demonstrate the kinds of
information you can expect to see in the
Extra value.
mysql>EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE->TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: VIEWS type: ALL possible_keys: NULL key: TABLE_SCHEMA,TABLE_NAME key_len: NULL ref: NULL rows: NULL Extra: Using where; Open_frm_only; Scanned 0 databases
Use of constant database and table lookup values enables the
server to avoid directory scans. For references to
VIEWS.TABLE_NAME, only the
.frm file need be opened.
mysql> EXPLAIN SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: TABLES
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Open_full_table; Scanned all databases
No lookup values are provided (there is no
WHERE clause), so the server must scan the
data directory and each database directory. For each table thus
identified, the table name and row format are selected.
TABLE_NAME requires no further table files to
be opened (the SKIP_OPEN_TABLE optimization
applies). ROW_FORMAT requires all table files
to be opened (OPEN_FULL_TABLE applies).
EXPLAIN reports
OPEN_FULL_TABLE because it is more expensive
than SKIP_OPEN_TABLE.
mysql>EXPLAIN SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES->WHERE TABLE_SCHEMA = 'test'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: TABLES type: ALL possible_keys: NULL key: TABLE_SCHEMA key_len: NULL ref: NULL rows: NULL Extra: Using where; Open_frm_only; Scanned 1 database
No table name lookup value is provided, so the server must scan
the test database directory. For the
TABLE_NAME and TABLE_TYPE
columns, the SKIP_OPEN_TABLE and
OPEN_FRM_ONLY optimizations apply,
respectively. EXPLAIN reports
OPEN_FRM_ONLY because it is more expensive.
mysql>EXPLAIN SELECT B.TABLE_NAME->FROM INFORMATION_SCHEMA.TABLES AS A, INFORMATION_SCHEMA.COLUMNS AS B->WHERE A.TABLE_SCHEMA = 'test'->AND A.TABLE_NAME = 't1'->AND B.TABLE_NAME = A.TABLE_NAME\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: A type: ALL possible_keys: NULL key: TABLE_SCHEMA,TABLE_NAME key_len: NULL ref: NULL rows: NULL Extra: Using where; Skip_open_table; Scanned 0 databases *************************** 2. row *************************** id: 1 select_type: SIMPLE table: B type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Using where; Open_frm_only; Scanned all databases; Using join buffer
For the first EXPLAIN output row:
Constant database and table lookup values enable the server to
avoid directory scans for TABLES values.
References to TABLES.TABLE_NAME require no
further table files.
For the second EXPLAIN output
row: All COLUMNS table values are
OPEN_FRM_ONLY lookups, so
COLUMNS.TABLE_NAME requires the
.frm file to be opened.
mysql> EXPLAIN SELECT * FROM INFORMATION_SCHEMA.COLLATIONS\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: COLLATIONS
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
In this case, no optimizations apply because
COLLATIONS is not one of the
INFORMATION_SCHEMA tables for which
optimizations are available.
This section lists a number of miscellaneous tips for improving query processing speed:
If your application makes several database requests to perform related updates, combining the statements into a stored routine can help performance. Similarly, if your application computes a single result based on several column values or large volumes of data, combining the computation into a UDF (user-defined function) can help performance. The resulting fast database operations are then available to be reused by other queries, applications, and even code written in different programming languages. See Section 20.2, “Using Stored Routines (Procedures and Functions)” and Section 24.4, “Adding New Functions to MySQL” for more information.
To fix any compression issues that occur with
ARCHIVE tables, use
OPTIMIZE TABLE. See
Section 15.6, “The ARCHIVE Storage Engine”.
If possible, classify reports as “live” or as “statistical”, where data needed for statistical reports is created only from summary tables that are generated periodically from the live data.
If you have data that does not conform well to a
rows-and-columns table structure, you can pack and store
data into a BLOB column. In
this case, you must provide code in your application to pack
and unpack information, but this might save I/O operations
to read and write the sets of related values.
With Web servers, store images and other binary assets as files, with the path name stored in the database rather than the file itself. Most Web servers are better at caching files than database contents, so using files is generally faster. (Although you must handle backups and storage issues yourself in this case.)
If you need really high speed, look at the low-level MySQL
interfaces. For example, by accessing the MySQL
InnoDB or MyISAM
storage engine directly, you could get a substantial speed
increase compared to using the SQL interface.
Replication can provide a performance benefit for some operations. You can distribute client retrievals among replication servers to split up the load. To avoid slowing down the master while making backups, you can make backups using a slave server. See Chapter 17, Replication.
The best way to improve the performance of
SELECT operations is to create
indexes on one or more of the columns that are tested in the
query. The index entries act like pointers to the table rows,
allowing the query to quickly determine which rows match a
condition in the WHERE clause, and retrieve the
other column values for those rows. All MySQL data types can be
indexed.
Although it can be tempting to create an indexes for every possible column used in a query, unnecessary indexes waste space and waste time for MySQL to determine which indexes to use. Indexes also add to the cost of inserts, updates, and deletes because each index must be updated. You must find the right balance to achieve fast queries using the optimal set of indexes.
Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially.
Most MySQL indexes (PRIMARY KEY,
UNIQUE, INDEX, and
FULLTEXT) are stored in B-trees. Exceptions
are that indexes on spatial data types use R-trees, and that
MEMORY tables also support hash indexes.
In general, indexes are used as described in the following
discussion. Characteristics specific to hash indexes (as used in
MEMORY tables) are described in
Section 8.3.8, “Comparison of B-Tree and Hash Indexes”.
MySQL uses indexes for these operations:
To find the rows matching a WHERE clause
quickly.
To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows (the most selective index).
If the table has a multiple-column index, any leftmost
prefix of the index can be used by the optimizer to look up
rows. For example, if you have a three-column index on
(col1, col2, col3), you have indexed
search capabilities on (col1),
(col1, col2), and (col1, col2,
col3). For more information, see
Section 8.3.5, “Multiple-Column Indexes”.
To retrieve rows from other tables when performing joins.
MySQL can use indexes on columns more efficiently if they
are declared as the same type and size. In this context,
VARCHAR and
CHAR are considered the same
if they are declared as the same size. For example,
VARCHAR(10) and
CHAR(10) are the same size, but
VARCHAR(10) and
CHAR(15) are not.
For comparisons between nonbinary string columns, both
columns should use the same character set. For example,
comparing a utf8 column with a
latin1 column precludes use of an index.
Comparison of dissimilar columns (comparing a string column
to a temporal or numeric column, for example) may prevent
use of indexes if values cannot be compared directly without
conversion. For a given value such as 1
in the numeric column, it might compare equal to any number
of values in the string column such as
'1', ' 1',
'00001', or '01.e1'.
This rules out use of any indexes for the string column.
To find the MIN() or
MAX() value for a specific
indexed column key_col. This is
optimized by a preprocessor that checks whether you are
using WHERE on all key
parts that occur before key_part_N =
constantkey_col
in the index. In this case, MySQL does a single key lookup
for each MIN() or
MAX() expression and replaces
it with a constant. If all expressions are replaced with
constants, the query returns at once. For example:
SELECT MIN(key_part2),MAX(key_part2) FROMtbl_nameWHEREkey_part1=10;
To sort or group a table if the sorting or grouping is done
on a leftmost prefix of a usable index (for example,
ORDER BY ). If all key
parts are followed by key_part1,
key_part2DESC, the key is
read in reverse order. See
Section 8.2.1.11, “ORDER BY Optimization”, and
Section 8.2.1.12, “GROUP BY Optimization”.
In some cases, a query can be optimized to retrieve values without consulting the data rows. (An index that provides all the necessary results for a query is called a covering index.) If a query uses from a table only columns that are included in some index, the selected values can be retrieved from the index tree for greater speed:
SELECTkey_part3FROMtbl_nameWHEREkey_part1=1
Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query. See Section 8.2.1.17, “How to Avoid Full Table Scans” for details.
The primary key for a table represents the column or set of
columns that you use in your most vital queries. It has an
associated index, for fast query performance. Query performance
benefits from the NOT NULL optimization,
because it cannot include any NULL values.
With the InnoDB storage engine, the table
data is physically organized to do ultra-fast lookups and sorts
based on the primary key column or columns.
If your table is big and important, but does not have an obvious column or set of columns to use as a primary key, you might create a separate column with auto-increment values to use as the primary key. These unique IDs can serve as pointers to corresponding rows in other tables when you join tables using foreign keys.
If a table has many columns, and you query many different combinations of columns, it might be efficient to split the less-frequently used data into separate tables with a few columns each, and relate them back to the main table by duplicating the numeric ID column from the main table. That way, each small table can have a primary key for fast lookups of its data, and you can query just the set of columns that you need using a join operation. Depending on how the data is distributed, the queries might perform less I/O and take up less cache memory because the relevant columns are packed together on disk. (To maximize performance, queries try to read as few data blocks as possible from disk; tables with only a few columns can fit more rows in each data block.)
The most common type of index involves a single column, storing
copies of the values from that column in a data structure,
allowing fast lookups for the rows with the corresponding column
values. The B-tree data structure lets the index quickly find a
specific value, a set of values, or a range of values,
corresponding to operators such as =,
>, ≤,
BETWEEN, IN, and so on, in
a WHERE clause.
The maximum number of indexes per table and the maximum index length is defined per storage engine. See Chapter 14, The InnoDB Storage Engine, and Chapter 15, Alternative Storage Engines. All storage engines support at least 16 indexes per table and a total index length of at least 256 bytes. Most storage engines have higher limits.
For additional information about column indexes, see Section 13.1.13, “CREATE INDEX Syntax”.
With
col_name(N)N characters of the column. Indexing
only a prefix of column values in this way can make the index
file much smaller. When you index a
BLOB or
TEXT column, you
must specify a prefix length for the index.
For example:
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
Prefixes can be up to 1000 bytes long (767 bytes for
InnoDB tables, unless you have
innodb_large_prefix set).
Prefix limits are measured in bytes, whereas the prefix length
in CREATE TABLE,
ALTER TABLE, and
CREATE INDEX statements is
interpreted as number of characters for nonbinary string types
(CHAR,
VARCHAR,
TEXT) and number of bytes for
binary string types (BINARY,
VARBINARY,
BLOB). Take this into account
when specifying a prefix length for a nonbinary string column
that uses a multibyte character set.
For additional information about index prefixes, see Section 13.1.13, “CREATE INDEX Syntax”.
FULLTEXT indexes are used for full-text
searches. Only the MyISAM storage engine
supports FULLTEXT indexes and only for
CHAR,
VARCHAR, and
TEXT columns. Indexing always
takes place over the entire column and column prefix indexing is
not supported. For details, see
Section 12.9, “Full-Text Search Functions”.
You can create indexes on spatial data types. Only
MyISAM supports R-tree indexes on spatial
types. Other storage engines use B-trees for indexing spatial
types (except for ARCHIVE, which does not
support spatial type indexing).
The MEMORY storage engine uses
HASH indexes by default, but also supports
BTREE indexes.
MySQL can create composite indexes (that is, indexes on multiple columns). An index may consist of up to 16 columns. For certain data types, you can index a prefix of the column (see Section 8.3.4, “Column Indexes”).
MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.
A multiple-column index can be considered a sorted array, the rows of which contain values that are created by concatenating the values of the indexed columns.
As an alternative to a composite index, you can introduce a column that is “hashed” based on information from other columns. If this column is short, reasonably unique, and indexed, it might be faster than a “wide” index on many columns. In MySQL, it is very easy to use this extra column:
SELECT * FROMtbl_nameWHEREhash_col=MD5(CONCAT(val1,val2)) ANDcol1=val1ANDcol2=val2;
Suppose that a table has the following specification:
CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name)
);
The name index is an index over the
last_name and first_name
columns. The index can be used for lookups in queries that
specify values in a known range for combinations of
last_name and first_name
values. It can also be used for queries that specify just a
last_name value because that column is a
leftmost prefix of the index (as described later in this
section). Therefore, the name index is used
for lookups in the following queries:
SELECT * FROM test WHERE last_name='Widenius'; SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael'; SELECT * FROM test WHERE last_name='Widenius' AND (first_name='Michael' OR first_name='Monty'); SELECT * FROM test WHERE last_name='Widenius' AND first_name >='M' AND first_name < 'N';
However, the name index is
not used for lookups in the following
queries:
SELECT * FROM test WHERE first_name='Michael'; SELECT * FROM test WHERE last_name='Widenius' OR first_name='Michael';
Suppose that you issue the following
SELECT statement:
SELECT * FROMtbl_nameWHERE col1=val1AND col2=val2;
If a multiple-column index exists on col1 and
col2, the appropriate rows can be fetched
directly. If separate single-column indexes exist on
col1 and col2, the
optimizer attempts to use the Index Merge optimization (see
Section 8.2.1.4, “Index Merge Optimization”), or attempts to find
the most restrictive index by deciding which index excludes more
rows and using that index to fetch the rows.
If the table has a multiple-column index, any leftmost prefix of
the index can be used by the optimizer to look up rows. For
example, if you have a three-column index on (col1,
col2, col3), you have indexed search capabilities on
(col1), (col1, col2), and
(col1, col2, col3).
MySQL cannot use the index to perform lookups if the columns do
not form a leftmost prefix of the index. Suppose that you have
the SELECT statements shown here:
SELECT * FROMtbl_nameWHERE col1=val1; SELECT * FROMtbl_nameWHERE col1=val1AND col2=val2; SELECT * FROMtbl_nameWHERE col2=val2; SELECT * FROMtbl_nameWHERE col2=val2AND col3=val3;
If an index exists on (col1, col2, col3),
only the first two queries use the index. The third and fourth
queries do involve indexed columns, but
(col2) and (col2, col3)
are not leftmost prefixes of (col1, col2,
col3).
Always check whether all your queries really use the indexes
that you have created in the tables. Use the
EXPLAIN statement, as described
in Section 8.8.1, “Optimizing Queries with EXPLAIN”.
Storage engines collect statistics about tables for use by the optimizer. Table statistics are based on value groups, where a value group is a set of rows with the same key prefix value. For optimizer purposes, an important statistic is the average value group size.
MySQL uses the average value group size in the following ways:
To estimate how may rows must be read for each
ref access
To estimate how many row a partial join will produce; that is, the number of rows that an operation of this form will produce:
(...) JOINtbl_nameONtbl_name.key=expr
As the average value group size for an index increases, the index is less useful for those two purposes because the average number of rows per lookup increases: For the index to be good for optimization purposes, it is best that each index value target a small number of rows in the table. When a given index value yields a large number of rows, the index is less useful and MySQL is less likely to use it.
The average value group size is related to table cardinality,
which is the number of value groups. The
SHOW INDEX statement displays a
cardinality value based on N/S, where
N is the number of rows in the table
and S is the average value group
size. That ratio yields an approximate number of value groups in
the table.
For a join based on the <=> comparison
operator, NULL is not treated differently
from any other value: NULL <=> NULL,
just as N <=>
NN.
However, for a join based on the = operator,
NULL is different from
non-NULL values:
expr1 =
expr2expr1 or
expr2 (or both) are
NULL. This affects
ref accesses for comparisons
of the form tbl_name.key =
exprexpr is NULL,
because the comparison cannot be true.
For = comparisons, it does not matter how
many NULL values are in the table. For
optimization purposes, the relevant value is the average size of
the non-NULL value groups. However, MySQL
does not currently enable that average size to be collected or
used.
For InnoDB and MyISAM
tables, you have some control over collection of table
statistics by means of the
innodb_stats_method and
myisam_stats_method system
variables, respectively. These variables have three possible
values, which differ as follows:
When the variable is set to nulls_equal,
all NULL values are treated as identical
(that is, they all form a single value group).
If the NULL value group size is much
higher than the average non-NULL value
group size, this method skews the average value group size
upward. This makes index appear to the optimizer to be less
useful than it really is for joins that look for
non-NULL values. Consequently, the
nulls_equal method may cause the
optimizer not to use the index for
ref accesses when it
should.
When the variable is set to
nulls_unequal, NULL
values are not considered the same. Instead, each
NULL value forms a separate value group
of size 1.
If you have many NULL values, this method
skews the average value group size downward. If the average
non-NULL value group size is large,
counting NULL values each as a group of
size 1 causes the optimizer to overestimate the value of the
index for joins that look for non-NULL
values. Consequently, the nulls_unequal
method may cause the optimizer to use this index for
ref lookups when other
methods may be better.
When the variable is set to
nulls_ignored, NULL
values are ignored.
If you tend to use many joins that use
<=> rather than =,
NULL values are not special in comparisons
and one NULL is equal to another. In this
case, nulls_equal is the appropriate
statistics method.
The innodb_stats_method system
variable has a global value; the
myisam_stats_method system
variable has both global and session values. Setting the global
value affects statistics collection for tables from the
corresponding storage engine. Setting the session value affects
statistics collection only for the current client connection.
This means that you can force a table's statistics to be
regenerated with a given method without affecting other clients
by setting the session value of
myisam_stats_method.
To regenerate MyISAM table statistics, you
can use any of the following methods:
Change the table to cause its statistics to go out of date
(for example, insert a row and then delete it), and then set
myisam_stats_method and
issue an ANALYZE TABLE
statement
Some caveats regarding the use of
innodb_stats_method and
myisam_stats_method:
You can force table statistics to be collected explicitly,
as just described. However, MySQL may also collect
statistics automatically. For example, if during the course
of executing statements for a table, some of those
statements modify the table, MySQL may collect statistics.
(This may occur for bulk inserts or deletes, or some
ALTER TABLE statements, for
example.) If this happens, the statistics are collected
using whatever value
innodb_stats_method or
myisam_stats_method has at
the time. Thus, if you collect statistics using one method,
but the system variable is set to the other method when a
table's statistics are collected automatically later, the
other method will be used.
There is no way to tell which method was used to generate statistics for a given table.
These variables apply only to InnoDB and
MyISAM tables. Other storage engines have
only one method for collecting table statistics. Usually it
is closer to the nulls_equal method.
Understanding the B-tree and hash data structures can help
predict how different queries perform on different storage
engines that use these data structures in their indexes,
particularly for the MEMORY storage engine
that lets you choose B-tree or hash indexes.
A B-tree index can be used for column comparisons in expressions
that use the =,
>,
>=,
<,
<=,
or BETWEEN operators. The index
also can be used for LIKE
comparisons if the argument to LIKE
is a constant string that does not start with a wildcard
character. For example, the following
SELECT statements use indexes:
SELECT * FROMtbl_nameWHEREkey_colLIKE 'Patrick%'; SELECT * FROMtbl_nameWHEREkey_colLIKE 'Pat%_ck%';
In the first statement, only rows with 'Patrick' <=
are
considered. In the second statement, only rows with
key_col < 'Patricl''Pat' <= are considered.
key_col <
'Pau'
The following SELECT statements
do not use indexes:
SELECT * FROMtbl_nameWHEREkey_colLIKE '%Patrick%'; SELECT * FROMtbl_nameWHEREkey_colLIKEother_col;
In the first statement, the LIKE
value begins with a wildcard character. In the second statement,
the LIKE value is not a constant.
If you use ... LIKE
'% and
string%'string is longer than three
characters, MySQL uses the Turbo
Boyer-Moore algorithm to initialize the pattern for
the string and then uses this pattern to perform the search more
quickly.
A search using col_name IS
NULLcol_name is indexed.
Any index that does not span all
AND levels in the
WHERE clause is not used to optimize the
query. In other words, to be able to use an index, a prefix of
the index must be used in every AND
group.
The following WHERE clauses use indexes:
... WHEREindex_part1=1 ANDindex_part2=2 ANDother_column=3 /*index= 1 ORindex= 2 */ ... WHEREindex=1 OR A=10 ANDindex=2 /* optimized like "index_part1='hello'" */ ... WHEREindex_part1='hello' ANDindex_part3=5 /* Can use index onindex1but not onindex2orindex3*/ ... WHEREindex1=1 ANDindex2=2 ORindex1=3 ANDindex3=3;
These WHERE clauses do
not use indexes:
/* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2
/* Index is not used in both parts of the WHERE clause */
... WHERE index=1 OR A=10
/* No index spans all rows */
... WHERE index_part1=1 OR index_part2=10
Sometimes MySQL does not use an index, even if one is available.
One circumstance under which this occurs is when the optimizer
estimates that using the index would require MySQL to access a
very large percentage of the rows in the table. (In this case, a
table scan is likely to be much faster because it requires fewer
seeks.) However, if such a query uses LIMIT
to retrieve only some of the rows, MySQL uses an index anyway,
because it can much more quickly find the few rows to return in
the result.
Hash indexes have somewhat different characteristics from those just discussed:
They are used only for equality comparisons that use the
= or <=>
operators (but are very fast). They are
not used for comparison operators such as
< that find a range of values. Systems
that rely on this type of single-value lookup are known as
“key-value stores”; to use MySQL for such
applications, use hash indexes wherever possible.
The optimizer cannot use a hash index to speed up
ORDER BY operations. (This type of index
cannot be used to search for the next entry in order.)
MySQL cannot determine approximately how many rows there are
between two values (this is used by the range optimizer to
decide which index to use). This may affect some queries if
you change a MyISAM or
InnoDB table to a hash-indexed
MEMORY table.
Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)
In your role as a database designer, look for the most efficient way to organize your schemas, tables, and columns. As when tuning application code, you minimize I/O, keep related items together, and plan ahead so that performance stays high as the data volume increases. Starting with an efficient database design makes it easier for team members to write high-performing application code, and makes the database likely to endure as applications evolve and are rewritten.
Design your tables to minimize their space on the disk. This can result in huge improvements by reducing the amount of data written to and read from disk. Smaller tables normally require less main memory while their contents are being actively processed during query execution. Any space reduction for table data also results in smaller indexes that can be processed faster.
MySQL supports many different storage engines (table types) and row formats. For each table, you can decide which storage and indexing method to use. Choosing the proper table format for your application can give you a big performance gain. See Chapter 14, The InnoDB Storage Engine, and Chapter 15, Alternative Storage Engines.
You can get better performance for a table and minimize storage space by using the techniques listed here:
Use the most efficient (smallest) data types possible. MySQL
has many specialized types that save disk space and memory.
For example, use the smaller integer types if possible to
get smaller tables. MEDIUMINT
is often a better choice than
INT because a
MEDIUMINT column uses 25%
less space.
Declare columns to be NOT NULL if
possible. It makes SQL operations faster, by enabling better
use of indexes and eliminating overhead for testing whether
each value is NULL. You also save some
storage space, one bit per column. If you really need
NULL values in your tables, use them.
Just avoid the default setting that allows
NULL values in every column.
InnoDB tables use a compact storage
format. By default, tables are created in the compact format
(ROW_FORMAT=COMPACT). If you wish to
downgrade to older versions of MySQL, you can request the
old format with ROW_FORMAT=REDUNDANT.
The presence of the compact row format decreases row storage space by about 20% at the cost of increasing CPU use for some operations. If your workload is a typical one that is limited by cache hit rates and disk speed it is likely to be faster. If it is a rare case that is limited by CPU speed, it might be slower.
The compact InnoDB format also changes
how CHAR columns containing
UTF-8 data are stored. With
ROW_FORMAT=REDUNDANT, a UTF-8
CHAR(
occupies 3 × N)N bytes, given
that the maximum length of a UTF-8 encoded character is
three bytes. Many languages can be written primarily using
single-byte UTF-8 characters, so a fixed storage length
often wastes space. With
ROW_FORMAT=COMPACT format,
InnoDB allocates a variable amount of
storage in the range from N to 3
× N bytes for these columns
by stripping trailing spaces if necessary. The minimum
storage length is kept as N bytes
to facilitate in-place updates in typical cases.
To minimize space even further by storing table data in
compressed form, specify
ROW_FORMAT=COMPRESSED when creating
InnoDB tables, or run the
myisampack command on an existing
MyISAM table. (InnoDB
tables compressed tables are readable and writable, while
MyISAM compressed tables are read-only.)
For MyISAM tables, if you do not have any
variable-length columns
(VARCHAR,
TEXT, or
BLOB columns), a fixed-size
row format is used. This is faster but may waste some space.
See Section 15.3.3, “MyISAM Table Storage Formats”. You can hint
that you want to have fixed length rows even if you have
VARCHAR columns with the
CREATE TABLE option
ROW_FORMAT=FIXED.
The primary index of a table should be as short as possible.
This makes identification of each row easy and efficient.
For InnoDB tables, the primary key
columns are duplicated in each secondary index entry, so a
short primary key saves considerable space if you have many
secondary indexes.
Create only the indexes that you need to improve query performance. Indexes are good for retrieval, but slow down insert and update operations. If you access a table mostly by searching on a combination of columns, create a single composite index on them rather than a separate index for each column. The first part of the index should be the column most used. If you always use many columns when selecting from the table, the first column in the index should be the one with the most duplicates, to obtain better compression of the index.
If it is very likely that a long string column has a unique prefix on the first number of characters, it is better to index only this prefix, using MySQL's support for creating an index on the leftmost part of the column (see Section 13.1.13, “CREATE INDEX Syntax”). Shorter indexes are faster, not only because they require less disk space, but because they also give you more hits in the index cache, and thus fewer disk seeks. See Section 8.12.2, “Tuning Server Parameters”.
In some circumstances, it can be beneficial to split into two a table that is scanned very often. This is especially true if it is a dynamic-format table and it is possible to use a smaller static format table that can be used to find the relevant rows when scanning the table.
Declare columns with identical information in different tables with identical data types, to speed up joins based on the corresponding columns.
Keep column names simple, so that you can use the same name
across different tables and simplify join queries. For
example, in a table named customer, use a
column name of name instead of
customer_name. To make your names
portable to other SQL servers, consider keeping them shorter
than 18 characters.
Normally, try to keep all data nonredundant (observing what is referred to in database theory as third normal form). Instead of repeating lengthy values such as names and addresses, assign them unique IDs, repeat these IDs as needed across multiple smaller tables, and join the tables in queries by referencing the IDs in the join clause.
If speed is more important than disk space and the maintenance costs of keeping multiple copies of data, for example in a business intelligence scenario where you analyze all the data from large tables, you can relax the normalization rules, duplicating information or creating summary tables to gain more speed.
For unique IDs or other values that can be represented as either strings or numbers, prefer numeric columns to string columns. Since large numeric values can be stored in fewer bytes than the corresponding strings, it is faster and takes less memory to transfer and compare them.
If you are using numeric data, it is faster in many cases to access information from a database (using a live connection) than to access a text file. Information in the database is likely to be stored in a more compact format than in the text file, so accessing it involves fewer disk accesses. You also save code in your application because you can avoid parsing the text file to find line and column boundaries.
For character and string columns, follow these guidelines:
Use binary collation order for fast comparison and sort operations, when you do not need language-specific collation features. You can use the BINARY operator to use binary collation within a particular query.
When comparing values from different columns, declare those columns with the same character set and collation wherever possible, to avoid string conversions while running the query.
For column values less than 8KB in size, use binary
VARCHAR instead of
BLOB. The GROUP BY
and ORDER BY clauses can generate
temporary tables, and these temporary tables can use the
MEMORY storage engine if the original
table does not contain any BLOB
columns.
If a table contains string columns such as name and address, but many queries do not retrieve those columns, consider splitting the string columns into a separate table and using join queries with a foreign key when necessary. When MySQL retrieves any value from a row, it reads a data block containing all the columns of that row (and possibly other adjacent rows). Keeping each row small, with only the most frequently used columns, allows more rows to fit in each data block. Such compact tables reduce disk I/O and memory usage for common queries.
When you use a randomly generated value as a primary key
in an InnoDB table, prefix it with an
ascending value such as the current date and time if
possible. When consecutive primary values are physically
stored near each other, InnoDB can
insert and retrieve them faster.
See Section 8.4.2.1, “Optimizing for Numeric Data” for reasons why a numeric column is usually preferable to an equivalent string column.
When storing a large blob containing textual data,
consider compressing it first. Do not use this technique
when the entire table is compressed by
InnoDB or MyISAM.
For a table with several columns, to reduce memory requirements for queries that do not use the BLOB column, consider splitting the BLOB column into a separate table and referencing it with a join query when needed.
Since the performance requirements to retrieve and display a BLOB value might be very different from other data types, you could put the BLOB-specific table on a different storage device or even a separate database instance. For example, to retrieve a BLOB might require a large sequential disk read that is better suited to a traditional hard drive than to an SSD device.
See Section 8.4.2.2, “Optimizing for Character and String Types” for reasons why a
binary VARCHAR column is sometimes
preferable to an equivalent BLOB column.
Rather than testing for equality against a very long text
string, you can store a hash of the column value in a
separate column, index that column, and test the hashed
value in queries. (Use the MD5() or
CRC32() function to produce the hash
value.) Since hash functions can produce duplicate results
for different inputs, you still include a clause
AND in
the query to guard against false matches; the performance
benefit comes from the smaller, easily scanned index for
the hashed values.
blob_column =
long_string_value
ANALYSE([
max_elements[,max_memory]])
ANALYSE() examines the result from a query
and returns an analysis of the results that suggests optimal
data types for each column that may help reduce table sizes.
To obtain this analysis, append PROCEDURE
ANALYSE to the end of a
SELECT statement:
SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])
For example:
SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);
The results show some statistics for the values returned by
the query, and propose an optimal data type for the columns.
This can be helpful for checking your existing tables, or
after importing new data. You may need to try different
settings for the arguments so that PROCEDURE
ANALYSE() does not suggest the
ENUM data type when it is not
appropriate.
The arguments are optional and are used as follows:
max_elements (default 256) is
the maximum number of distinct values that
ANALYSE() notices per column. This is
used by ANALYSE() to check whether the
optimal data type should be of type
ENUM; if there are more
than max_elements distinct
values, then ENUM is not a
suggested type.
max_memory (default 8192) is
the maximum amount of memory that
ANALYSE() should allocate per column
while trying to find all distinct values.
A PROCEDURE clause is not permitted in a
UNION statement.
Some techniques for keeping individual queries fast involve splitting data across many tables. When the number of tables runs into the thousands or even millions, the overhead of dealing with all these tables becomes a new performance consideration.
When you execute a mysqladmin status command, you should see something like this:
Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
The Open tables value of 12 can be somewhat
puzzling if you have only six tables.
MySQL is multi-threaded, so there may be many clients issuing
queries for a given table simultaneously. To minimize the
problem with multiple client sessions having different states
on the same table, the table is opened independently by each
concurrent session. This uses additional memory but normally
increases performance. With MyISAM tables,
one extra file descriptor is required for the data file for
each client that has the table open. (By contrast, the index
file descriptor is shared between all sessions.)
The table_open_cache and
max_connections system
variables affect the maximum number of files the server keeps
open. If you increase one or both of these values, you may run
up against a limit imposed by your operating system on the
per-process number of open file descriptors. Many operating
systems permit you to increase the open-files limit, although
the method varies widely from system to system. Consult your
operating system documentation to determine whether it is
possible to increase the limit and how to do so.
table_open_cache is related
to max_connections. For
example, for 200 concurrent running connections, specify a
table cache size of at least 200 *
, where
NN is the maximum number of tables
per join in any of the queries which you execute. You must
also reserve some extra file descriptors for temporary tables
and files.
Make sure that your operating system can handle the number of
open file descriptors implied by the
table_open_cache setting. If
table_open_cache is set too
high, MySQL may run out of file descriptors and refuse
connections, fail to perform queries, and be very unreliable.
You should also take into account the fact that the
MyISAM storage engine needs two file
descriptors for each unique open table. For a partitioned
MyISAM table, two file descriptors are
required for each partition of the opened table. (Note further
that when MyISAM opens a partitioned table,
it opens every partition of this table, whether or not a given
partition is actually used. See
MyISAM and partition file descriptor usage.)
You can increase the number of file descriptors available to
MySQL using the
--open-files-limit startup
option to mysqld. See
Section B.5.2.18, “File Not Found and Similar Errors”.
The cache of open tables is kept at a level of
table_open_cache entries. The
default value is 400; this can be changed with the
--table_open_cache option to
mysqld. Note that MySQL may temporarily
open more tables than this to execute queries.
MySQL closes an unused table and removes it from the table cache under the following circumstances:
When the cache is full and a thread tries to open a table that is not in the cache.
When the cache contains more than
table_open_cache entries
and a table in the cache is no longer being used by any
threads.
When a table flushing operation occurs. This happens when
someone issues a
FLUSH
TABLES statement or executes a
mysqladmin flush-tables or
mysqladmin refresh command.
When the table cache fills up, the server uses the following procedure to locate a cache entry to use:
Tables that are not currently in use are released, beginning with the table least recently used.
If a new table needs to be opened, but the cache is full and no tables can be released, the cache is temporarily extended as necessary. When the cache is in a temporarily extended state and a table goes from a used to unused state, the table is closed and released from the cache.
A MyISAM table is opened for each
concurrent access. This means the table needs to be opened
twice if two threads access the same table or if a thread
accesses the table twice in the same query (for example, by
joining the table to itself). Each concurrent open requires an
entry in the table cache. The first open of any
MyISAM table takes two file descriptors:
one for the data file and one for the index file. Each
additional use of the table takes only one file descriptor for
the data file. The index file descriptor is shared among all
threads.
If you are opening a table with the HANDLER
statement,
a dedicated table object is allocated for the thread. This
table object is not shared by other threads and is not closed
until the thread calls tbl_name OPENHANDLER
or the
thread terminates. When this happens, the table is put back in
the table cache (if the cache is not full). See
Section 13.2.4, “HANDLER Syntax”.
tbl_name CLOSE
You can determine whether your table cache is too small by
checking the mysqld status variable
Opened_tables, which
indicates the number of table-opening operations since the
server started:
mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741 |
+---------------+-------+
If the value is very large or increases rapidly, even when you
have not issued many
FLUSH TABLES
statements, increase the table cache size. See
Section 5.1.4, “Server System Variables”, and
Section 5.1.6, “Server Status Variables”.
If you have many MyISAM tables in the same
database directory, open, close, and create operations are
slow. If you execute SELECT
statements on many different tables, there is a little
overhead when the table cache is full, because for every table
that has to be opened, another must be closed. You can reduce
this overhead by increasing the number of entries permitted in
the table cache.
In some cases, the server creates internal temporary tables while processing statements. Users have no direct control over when this occurs.
The server creates temporary tables under conditions such as these:
Evaluation of UNION
statements.
Evaluation of some views, such those that use the
TEMPTABLE algorithm,
UNION, or aggregation.
Evaluation of statements that contain an ORDER
BY clause and a different GROUP
BY clause, or for which the ORDER
BY or GROUP BY contains columns
from tables other than the first table in the join queue.
Evaluation of DISTINCT combined with
ORDER BY may require a temporary table.
For queries that use the SQL_SMALL_RESULT
option, MySQL uses an in-memory temporary table, unless the
query also contains elements (described later) that require
on-disk storage.
Evaluation of multiple-table
UPDATE statements.
Evaluation of GROUP_CONCAT()
or COUNT(DISTINCT)
expressions.
To determine whether a statement requires a temporary table, use
EXPLAIN and check the
Extra column to see whether it says
Using temporary (see
Section 8.8.1, “Optimizing Queries with EXPLAIN”).
When the server creates an internal temporary table (either in
memory or on disk), it increments the
Created_tmp_tables status
variable. If the server creates the table on disk (either
initially or by converting an in-memory table) it increments the
Created_tmp_disk_tables status
variable.
Some query conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:
Presence of any string column in a GROUP
BY or DISTINCT clause larger
than 512 bytes
Presence of any string column with a maximum length larger
than 512 (bytes for binary strings, characters for nonbinary
strings) in the SELECT list,
if UNION or
UNION ALL
is used
The SHOW COLUMNS and
DESCRIBE statements use
BLOB as the type for some columns, thus
the temporary table used for the results is an on-disk
table.
An internal temporary table can be held in memory and
processed by the MEMORY storage engine, or
stored on disk and processed by the MyISAM
storage engine.
If an internal temporary table is created as an in-memory
table but becomes too large, MySQL automatically converts it
to an on-disk table. The maximum size for in-memory temporary
tables is determined from whichever of the values of
tmp_table_size and
max_heap_table_size is
smaller. This differs from MEMORY tables
explicitly created with CREATE
TABLE: For such tables, only the
max_heap_table_size system
variable determines how large the table is permitted to grow
and there is no conversion to on-disk format.
In-memory temporary tables are managed by the
MEMORY storage engine, which uses
fixed-length row format. VARCHAR and
VARBINARY column values are padded to the
maximum column length, in effect storing them as
CHAR and BINARY columns.
On-disk temporary tables are managed by the
MyISAM storage engine using dynamic-width
row format. Columns take only as much storage as needed, which
reduces disk I/O and space requirements, and processing time
compared to on-disk tables that use fixed-length rows. The
exception to dynamic-length row format is that on-disk
temporary tables for derived tables are stored using
fixed-length rows.
Prior to MySQL 5.5.47, on-disk temporary table storage uses fixed-length rows.
For statements that initially create an internal temporary
table in memory, then convert it to an on-disk table, better
performance might be achieved by skipping the conversion step
and creating the table on disk to begin with. The
big_tables system variable
can be used to force disk storage of internal temporary
tables.
InnoDB is the storage engine that
MySQL customers typically use in production databases where
reliability and concurrency are important. Because
InnoDB is the default storage engine in MySQL
5.5 and higher, you can expect to see InnoDB
tables more often than before. This section explains how to
optimize database operations for InnoDB tables.
Once your data reaches a stable size, or a growing table has
increased by tens or some hundreds of megabytes, consider
using the OPTIMIZE TABLE statement to
reorganize the table and compact any wasted space. The
reorganized tables require less disk I/O to perform full
table scans. This is a straightforward technique that can
improve performance when other techniques such as improving
index usage or tuning application code are not practical.
OPTIMIZE TABLE copies the data part of
the table and rebuilds the indexes. The benefits come from
improved packing of data within indexes, and reduced
fragmentation within the tablespaces and on disk. The
benefits vary depending on the data in each table. You may
find that there are significant gains for some and not for
others, or that the gains decrease over time until you next
optimize the table. This operation can be slow if the table
is large or if the indexes being rebuilt do not fit into the
buffer pool. The first run after adding a lot of data to a
table is often much slower than later runs.
In InnoDB, having a long PRIMARY
KEY (either a single column with a lengthy value,
or several columns that form a long composite value) wastes
a lot of disk space. The primary key value for a row is
duplicated in all the secondary index records that point to
the same row. (See Section 14.11.9, “Clustered and Secondary Indexes”.)
Create an AUTO_INCREMENT column as the
primary key if your primary key is long, or index a prefix
of a long VARCHAR column instead of the
entire column.
Use the VARCHAR data type
instead of CHAR to store
variable-length strings or for columns with many
NULL values. A
CHAR(
column always takes N)N characters
to store data, even if the string is shorter or its value is
NULL. Smaller tables fit better in the
buffer pool and reduce disk I/O.
When using COMPACT row format (the
default InnoDB format) and
variable-length character sets, such as
utf8 or sjis,
CHAR(
columns occupy a variable amount of space, but still at
least N)N bytes.
For tables that are big, or contain lots of repetitive text
or numeric data, consider using
COMPRESSED row format. Less disk I/O is
required to bring data into the buffer pool, or to perform
full table scans. Before making a permanent decision,
measure the amount of compression you can achieve by using
COMPRESSED versus
COMPACT row format.
To optimize InnoDB transaction processing,
find the ideal balance between the performance overhead of
transactional features and the workload of your server. For
example, an application might encounter performance issues if it
commits thousands of times per second, and different performance
issues if it commits only every 2-3 hours.
The default MySQL setting AUTOCOMMIT=1
can impose performance limitations on a busy database
server. Where practical, wrap several related data change
operations into a single transaction, by issuing
SET AUTOCOMMIT=0 or a START
TRANSACTION statement, followed by a
COMMIT statement after making all the
changes.
InnoDB must flush the log to disk at each
transaction commit if that transaction made modifications to
the database. When each change is followed by a commit (as
with the default autocommit setting), the I/O throughput of
the storage device puts a cap on the number of potential
operations per second.
Avoid performing rollbacks after inserting, updating, or deleting huge numbers of rows. If a big transaction is slowing down server performance, rolling it back can make the problem worse, potentially taking several times as long to perform as the original data change operations. Killing the database process does not help, because the rollback starts again on server startup.
To minimize the chance of this issue occurring:
Increase the size of the buffer pool so that all the data change changes can be cached rather than immediately written to disk.
Set
innodb_change_buffering=all
so that update and delete operations are buffered in
addition to inserts.
Consider issuing COMMIT statements
periodically during the big data change operation,
possibly breaking a single delete or update into
multiple statements that operate on smaller numbers of
rows.
To get rid of a runaway rollback once it occurs, increase
the buffer pool so that the rollback becomes CPU-bound and
runs fast, or kill the server and restart with
innodb_force_recovery=3, as
explained in Section 14.21.1, “The InnoDB Recovery Process”.
This issue is expected to be less prominent in MySQL 5.5 and
up, or in MySQL 5.1 with the InnoDB Plugin, because the
default setting
innodb_change_buffering=all
allows update and delete operations to be cached in memory,
making them faster to perform in the first place, and also
faster to roll back if needed. Make sure to use this
parameter setting on servers that process long-running
transactions with many inserts, updates, or deletes.
If you can afford the loss of some of the latest committed
transactions if a crash occurs, you can set the
innodb_flush_log_at_trx_commit
parameter to 0. InnoDB tries to flush the
log once per second anyway, although the flush is not
guaranteed. Also, set the value of
innodb_support_xa to 0,
which will reduce the number of disk flushes due to
synchronizing on disk data and the binary log.
When rows are modified or deleted, the rows and associated
undo logs are not
physically removed immediately, or even immediately after
the transaction commits. The old data is preserved until
transactions that started earlier or concurrently are
finished, so that those transactions can access the previous
state of modified or deleted rows. Thus, a long-running
transaction can prevent InnoDB from
purging data that was changed by a different transaction.
When rows are modified or deleted within a long-running
transaction, other transactions using the
READ COMMITTED and
REPEATABLE READ isolation
levels have to do more work to reconstruct the older data if
they read those same rows.
When a long-running transaction modifies a table, queries against that table from other transactions do not make use of the covering index technique. Queries that normally could retrieve all the result columns from a secondary index, instead look up the appropriate values from the table data.
If secondary index pages are found to have a
PAGE_MAX_TRX_ID that is too new, or if
records in the secondary index are delete-marked,
InnoDB may need to look up records using
a clustered index.
Consider the following guidelines for optimizing redo logging:
Make your redo log files big, even as big as the
buffer pool. When
InnoDB has written the redo log files
full, it must write the modified contents of the buffer pool
to disk in a
checkpoint. Small
redo log files cause many unnecessary disk writes. Although
historically big redo log files caused lengthy recovery
times, recovery is now much faster and you can confidently
use large redo log files.
The size and number of redo log files are configured using
the innodb_log_file_size
and
innodb_log_files_in_group
configuration options. For information about modifying an
existing redo log file configuration, see
Section 14.10.2, “Changing the Number or Size of InnoDB Redo Log Files”.
Consider increasing the size of the
log buffer. A large
log buffer enables large
transactions to run
without a need to write the log to disk before the
transactions commit.
Thus, if you have transactions that update, insert, or
delete many rows, making the log buffer larger saves disk
I/O. Log buffer size is configured using the
innodb_log_buffer_size
configuration option.
These performance tips supplement the general guidelines for fast inserts in Section 8.2.2.1, “Speed of INSERT Statements”.
When importing data into InnoDB, turn off
autocommit mode, because it performs a log flush to disk for
every insert. To disable autocommit during your import
operation, surround it with
SET
autocommit and
COMMIT statements:
SET autocommit=0;
... SQL import statements ...
COMMIT;
The mysqldump option
--opt creates dump files
that are fast to import into an InnoDB
table, even without wrapping them with the
SET
autocommit and
COMMIT statements.
If you have UNIQUE constraints on
secondary keys, you can speed up table imports by
temporarily turning off the uniqueness checks during the
import session:
SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;
For big tables, this saves a lot of disk I/O because
InnoDB can use its change buffer to write
secondary index records in a batch. Be certain that the data
contains no duplicate keys.
If you have FOREIGN KEY constraints in
your tables, you can speed up table imports by turning off
the foreign key checks for the duration of the import
session:
SET foreign_key_checks=0;
... SQL import statements ...
SET foreign_key_checks=1;
For big tables, this can save a lot of disk I/O.
Use the multiple-row INSERT
syntax to reduce communication overhead between the client
and the server if you need to insert many rows:
INSERT INTO yourtable VALUES (1,2), (5,5), ...;
This tip is valid for inserts into any table, not just
InnoDB tables.
When doing bulk inserts into tables with auto-increment
columns, set
innodb_autoinc_lock_mode to
2 instead of the default value 1. See
Section 14.11.6, “AUTO_INCREMENT Handling in InnoDB” for
details.
When performing bulk inserts, it is faster to insert rows in
PRIMARY KEY order.
InnoDB tables use a
clustered index,
which makes it relatively fast to use data in the order of
the PRIMARY KEY. Performing bulk inserts
in PRIMARY KEY order is particularly
important for tables that do not fit entirely within the
buffer pool.
To tune queries for InnoDB tables, create an
appropriate set of indexes on each table. See
Section 8.3.1, “How MySQL Uses Indexes” for details. Follow these
guidelines for InnoDB indexes:
Because each InnoDB table has a
primary key (whether
you request one or not), specify a set of primary key
columns for each table, columns that are used in the most
important and time-critical queries.
Do not specify too many or too long columns in the primary key, because these column values are duplicated in each secondary index. When an index contains unnecessary data, the I/O to read this data and memory to cache it reduce the performance and scalability of the server.
Do not create a separate secondary index for each column, because each query can only make use of one index. Indexes on rarely tested columns or columns with only a few different values might not be helpful for any queries. If you have many queries for the same table, testing different combinations of columns, try to create a small number of concatenated indexes rather than a large number of single-column indexes. If an index contains all the columns needed for the result set (known as a covering index), the query might be able to avoid reading the table data at all.
If an indexed column cannot contain any
NULL values, declare it as NOT
NULL when you create the table. The optimizer can
better determine which index is most effective to use for a
query, when it knows whether each column contains
NULL values.
If you often have recurring queries for tables that are not updated frequently, enable the query cache:
[mysqld] query_cache_type = 1 query_cache_size = 10M
For DDL operations on tables and indexes
(CREATE, ALTER, and
DROP statements), the most significant
aspect for InnoDB tables is that creating
and dropping secondary indexes is much faster in MySQL 5.5
and higher, than in MySQL 5.1 and before. See
Section 14.16, “InnoDB Fast Index Creation” for details.
“Fast index creation” makes it faster in some cases to drop an index before loading data into a table, then re-create the index after loading the data.
Use TRUNCATE TABLE to empty a
table, not DELETE FROM
. Foreign key
constraints can make a tbl_nameTRUNCATE statement
work like a regular DELETE statement, in
which case a sequence of commands like
DROP TABLE and
CREATE TABLE might be
fastest.
Because the primary key is integral to the storage layout of
each InnoDB table, and changing the
definition of the primary key involves reorganizing the
whole table, always set up the primary key as part of the
CREATE TABLE statement, and
plan ahead so that you do not need to
ALTER or DROP the
primary key afterward.
If you follow the best practices for database design and the
tuning techniques for SQL operations, but your database is still
slowed by heavy disk I/O activity, explore these low-level
techniques related to disk I/O. If the Unix
top tool or the Windows Task Manager shows
that the CPU usage percentage with your workload is less than
70%, your workload is probably disk-bound.
When table data is cached in the InnoDB
buffer pool, it can be accessed repeatedly by queries
without requiring any disk I/O. Specify the size of the
buffer pool with the
innodb_buffer_pool_size
option. This memory area is important enough that busy
databases often specify a size approximately 80% of the
amount of physical memory. For more information, see
Section 14.9.2.1, “The InnoDB Buffer Pool”.
In some versions of GNU/Linux and Unix, flushing files to
disk with the Unix fsync() call (which
InnoDB uses by default) and similar
methods is surprisingly slow. If database write performance
is an issue, conduct benchmarks with the
innodb_flush_method
parameter set to O_DSYNC.
When using the InnoDB storage engine on
Solaris 10 for x86_64 architecture (AMD Opteron), use direct
I/O for InnoDB-related files, to avoid
degradation of InnoDB performance. To use
direct I/O for an entire UFS file system used for storing
InnoDB-related files, mount it with the
forcedirectio option; see
mount_ufs(1M). (The default on Solaris
10/x86_64 is not to use this option.)
To apply direct I/O only to InnoDB file
operations rather than the whole file system, set
innodb_flush_method =
O_DIRECT. With this setting,
InnoDB calls
directio() instead of
fcntl() for I/O to data files (not for
I/O to log files).
When using the InnoDB storage engine with
a large
innodb_buffer_pool_size
value on any release of Solaris 2.6 and up and any platform
(sparc/x86/x64/amd64), conduct benchmarks with
InnoDB data files and log files on raw
devices or on a separate direct I/O UFS file system, using
the forcedirectio mount option as
described earlier. (It is necessary to use the mount option
rather than setting
innodb_flush_method if you
want direct I/O for the log files.) Users of the Veritas
file system VxFS should use the
convosync=direct mount option.
Do not place other MySQL data files, such as those for
MyISAM tables, on a direct I/O file
system. Executables or libraries must
not be placed on a direct I/O file system.
If you have additional storage devices available to set up a RAID configuration or symbolic links to different disks, Section 8.12.3, “Optimizing Disk I/O” for additional low-level I/O tips.
If throughput drops periodically because of
InnoDB
checkpoint
operations, consider increasing the value of the
innodb_io_capacity
configuration option. Higher values cause more frequent
flushing, avoiding the
backlog of work that can cause dips in throughput.
If the system is not falling behind with
InnoDB
flushing operations,
consider lowering the value of the
innodb_io_capacity
configuration option. Typically, you keep this option value
as low as practical, but not so low that it causes periodic
drops in throughput as mentioned in the preceding bullet. In
a typical scenario where you could lower the option value,
you might see a combination like this in the output from
SHOW ENGINE
INNODB STATUS:
History list length low, below a few thousand.
Insert buffer merges close to rows inserted.
Modified pages in buffer pool consistently well below
innodb_max_dirty_pages_pct
of the buffer pool. (Measure at a time when the server
is not doing bulk inserts; it is normal during bulk
inserts for the modified pages percentage to rise
significantly.)
Log sequence number - Last checkpoint
is at less than 7/8 or ideally less than 6/8 of the
total size of the InnoDB
log files.
Other InnoDB configuration options to
consider when tuning I/O-bound workloads include
innodb_adaptive_flushing,
innodb_change_buffering,
innodb_log_buffer_size,
innodb_log_file_size,
innodb_max_dirty_pages_pct,
innodb_max_purge_lag,
innodb_open_files,
innodb_random_read_ahead,
innodb_read_ahead_threshold,
innodb_read_io_threads,
innodb_rollback_segments,
innodb_write_io_threads,
and sync_binlog.
Different settings work best for servers with light, predictable loads, versus servers that are running near full capacity all the time, or that experience spikes of high activity.
Because the InnoDB storage engine performs
many of its optimizations automatically, many performance-tuning
tasks involve monitoring to ensure that the database is
performing well, and changing configuration options when
performance drops. See
Section 14.19, “InnoDB Integration with MySQL Performance Schema” for information
about detailed InnoDB performance monitoring.
For a list of the most important and most recent
InnoDB performance features, see
Section 1.4, “What Is New in MySQL 5.5”. Even if you have used
InnoDB tables in prior versions, these
features might be new to you, because they are from the
“InnoDB Plugin”. The Plugin co-existed alongside
the built-in InnoDB in MySQL 5.1, and becomes
the default storage engine in MySQL 5.5 and higher.
The main configuration steps you can perform include:
Enabling InnoDB to use high-performance
memory allocators on systems that include them. See
Section 14.9.3, “Configuring the Memory Allocator for InnoDB”.
Controlling the types of data change operations for which
InnoDB buffers the changed data, to avoid
frequent small disk writes. See
Section 14.9.4, “Configuring InnoDB Change Buffering”.
Because the default is to buffer all types of data change
operations, only change this setting if you need to reduce
the amount of buffering.
Turning the adaptive hash indexing feature on and off using
the
innodb_adaptive_hash_index
option. See Section 14.7.3, “Adaptive Hash Index” for more
information. You might change this setting during periods of
unusual activity, then restore it to its original setting.
Setting a limit on the number of concurrent threads that
InnoDB processes, if context switching is
a bottleneck. See
Section 14.9.5, “Configuring Thread Concurrency for InnoDB”.
Controlling the amount of prefetching that
InnoDB does with its read-ahead
operations. When the system has unused I/O capacity, more
read-ahead can improve the performance of queries. Too much
read-ahead can cause periodic drops in performance on a
heavily loaded system. See
Section 14.9.2.4, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”.
Increasing the number of background threads for read or write operations, if you have a high-end I/O subsystem that is not fully utilized by the default values. See Section 14.9.6, “Configuring the Number of Background InnoDB I/O Threads”.
Controlling how much I/O InnoDB performs
in the background. See
Section 14.9.7, “Configuring the InnoDB Master Thread I/O Rate”. The
amount of background I/O is higher than in MySQL 5.1, so you
might scale back this setting if you observe periodic drops
in performance.
Controlling the algorithm that determines when
InnoDB performs certain types of
background writes. See
Section 14.9.2.5, “Configuring InnoDB Buffer Pool Flushing”. The
algorithm works for some types of workloads but not others,
so might turn off this setting if you observe periodic drops
in performance.
Taking advantage of multicore processors and their cache memory configuration, to minimize delays in context switching. See Section 14.9.8, “Configuring Spin Lock Polling”.
Preventing one-time operations such as table scans from
interfering with the frequently accessed data stored in the
InnoDB buffer cache. See
Section 14.9.2.3, “Making the Buffer Pool Scan Resistant”.
Adjusting log files to a size that makes sense for
reliability and crash recovery. InnoDB
log files have often been kept small to avoid long startup
times after a crash. Optimizations introduced in MySQL 5.5.4
speed up certain steps of the crash
recovery process.
In particular, scanning the
redo log and applying
the redo log are faster due to improved algorithms for
memory management. If you have kept your log files
artificially small to avoid long startup times, you can now
consider increasing log file size to reduce the I/O that
occurs due recycling of redo log records.
Configuring the size and number of instances for the
InnoDB buffer pool, especially important
for systems with multi-gigabyte buffer pools. See
Section 14.9.2.2, “Configuring Multiple Buffer Pool Instances”.
Increasing the maximum number of concurrent transactions, which dramatically improves scalability for the busiest databases. See Section 14.7.8, “Undo Log”. Although this feature does not require any action during day-to-day operation, you must perform a slow shutdown during or after upgrading the database to MySQL 5.5 to enable the higher limit.
Moving purge operations (a type of garbage collection) into a background thread. See Section 14.9.9, “Configuring InnoDB Purge Scheduling”. To effectively measure the results of this setting, tune the other I/O-related and thread-related configuration settings first.
Reducing the amount of switching that
InnoDB does between concurrent threads,
so that SQL operations on a busy server do not queue up and
form a “traffic jam”. Set a value for the
innodb_thread_concurrency
option, up to approximately 32 for a high-powered modern
system. Increase the value for the
innodb_concurrency_tickets
option, typically to 5000 or so. This combination of options
sets a cap on the number of threads that
InnoDB processes at any one time, and
allows each thread to do substantial work before being
swapped out, so that the number of waiting threads stays low
and operations can complete without excessive context
switching.
InnoDB computes index
cardinality values
for a table the first time that table is accessed after
startup, instead of storing such values in the table. This
step can take significant time on systems that partition the
data into many tables. Since this overhead only applies to
the initial table open operation, to “warm up”
a table for later use, access it immediately after startup
by issuing a statement such as SELECT 1 FROM
.
tbl_name LIMIT 1
The MyISAM storage engine performs
best with read-mostly data or with low-concurrency operations,
because table locks limit the ability to perform simultaneous
updates. In MySQL 5.5,
InnoDB is the default storage engine
rather than MyISAM.
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
and examining
the tbl_nameCardinality 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.
Try to avoid complex SELECT
queries on MyISAM tables that are updated
frequently, to avoid problems with table locking that occur
due to contention between readers and writers.
MyISAM supports concurrent inserts: If a
table has no free blocks in the middle of the data file, you
can INSERT new rows into it
at the same time that other threads are reading from the
table. If it is important to be able to do this, consider
using the table in ways that avoid deleting rows. Another
possibility is to run OPTIMIZE
TABLE to defragment the table after you have
deleted a lot of rows from it. This behavior is altered by
setting the
concurrent_insert variable.
You can force new rows to be appended (and therefore permit
concurrent inserts), even in tables that have deleted rows.
See Section 8.11.3, “Concurrent Inserts”.
For MyISAM tables that change frequently,
try to avoid all variable-length columns
(VARCHAR,
BLOB, and
TEXT). The table uses dynamic
row format if it includes even a single variable-length
column. See Chapter 15, Alternative Storage Engines.
It is normally not useful to split a table into different
tables just because the rows become large. In accessing a
row, the biggest performance hit is the disk seek needed to
find the first byte of the row. After finding the data, most
modern disks can read the entire row fast enough for most
applications. The only cases where splitting up a table
makes an appreciable difference is if it is a
MyISAM table using dynamic row format
that you can change to a fixed row size, or if you very
often need to scan the table but do not need most of the
columns. See Chapter 15, Alternative Storage Engines.
Use ALTER TABLE ... ORDER BY
if you
usually retrieve rows in
expr1,
expr2, ...expr1,
expr2, ...
If you often need to calculate results such as counts based on information from a lot of rows, it may be preferable to introduce a new table and update the counter in real time. An update of the following form is very fast:
UPDATEtbl_nameSETcount_col=count_col+1 WHEREkey_col=constant;
This is very important when you use a MySQL storage engine
such as MyISAM that has only table-level
locking (multiple readers with single writers). This also
gives better performance with most database systems, because
the row locking manager in this case has less to do.
Use INSERT DELAYED when you
do not need to know when your data is written. This reduces
the overall insertion impact because many rows can be
written with a single disk write.
Use INSERT LOW_PRIORITY when you want to
give SELECT statements higher
priority than your inserts.
Use SELECT HIGH_PRIORITY to get
retrievals that jump the queue. That is, the
SELECT is executed even if
there is another client waiting to do a write.
LOW_PRIORITY and
HIGH_PRIORITY have an effect only for
storage engines that use only table-level locking (such as
MyISAM, MEMORY, and
MERGE).
Use OPTIMIZE TABLE
periodically to avoid fragmentation with dynamic-format
MyISAM tables. See
Section 15.3.3, “MyISAM Table Storage Formats”.
Declaring a MyISAM table with the
DELAY_KEY_WRITE=1 table option makes
index updates faster because they are not flushed to disk
until the table is closed. The downside is that if something
kills the server while such a table is open, you must ensure
that the table is okay by running the server with the
--myisam-recover-options
option, or by running myisamchk before
restarting the server. (However, even in this case, you
should not lose anything by using
DELAY_KEY_WRITE, because the key
information can always be generated from the data rows.)
Strings are automatically prefix- and end-space compressed
in MyISAM indexes. See
Section 13.1.13, “CREATE INDEX Syntax”.
You can increase performance by caching queries or answers in your application and then executing many inserts or updates together. Locking the table during this operation ensures that the index cache is only flushed once after all updates. You can also take advantage of MySQL's query cache to achieve similar results; see Section 8.10.3, “The MySQL Query Cache”.
These performance tips supplement the general guidelines for fast inserts in Section 8.2.2.1, “Speed of INSERT Statements”.
To improve performance when multiple clients insert a lot of
rows, use the INSERT DELAYED
statement. See Section 13.2.5.2, “INSERT DELAYED Syntax”. This
technique works for MyISAM and some other
storage engines, but not InnoDB.
For a MyISAM table, you can use
concurrent inserts to add rows at the same time that
SELECT statements are
running, if there are no deleted rows in middle of the data
file. See Section 8.11.3, “Concurrent Inserts”.
With some extra work, it is possible to make
LOAD DATA
INFILE run even faster for a
MyISAM table when the table has many
indexes. Use the following procedure:
Execute a FLUSH
TABLES statement or a mysqladmin
flush-tables command.
Use myisamchk --keys-used=0 -rq
/path/to/db/tbl_name
to remove all use of indexes for the table.
Insert data into the table with
LOAD DATA
INFILE. This does not update any indexes and
therefore is very fast.
If you intend only to read from the table in the future, use myisampack to compress it. See Section 15.3.3.3, “Compressed Table Characteristics”.
Re-create the indexes with myisamchk -rq
/path/to/db/tbl_name.
This creates the index tree in memory before writing it
to disk, which is much faster than updating the index
during LOAD
DATA INFILE because it avoids lots of disk
seeks. The resulting index tree is also perfectly
balanced.
Execute a FLUSH
TABLES statement or a mysqladmin
flush-tables command.
LOAD DATA
INFILE performs the preceding optimization
automatically if the MyISAM table into
which you insert data is empty. The main difference between
automatic optimization and using the procedure explicitly is
that you can let myisamchk allocate much
more temporary memory for the index creation than you might
want the server to allocate for index re-creation when it
executes the LOAD
DATA INFILE statement.
You can also disable or enable the nonunique indexes for a
MyISAM table by using the following
statements rather than myisamchk. If you
use these statements, you can skip the
FLUSH TABLE
operations:
ALTER TABLEtbl_nameDISABLE KEYS; ALTER TABLEtbl_nameENABLE KEYS;
To speed up INSERT operations
that are performed with multiple statements for
nontransactional tables, lock your tables:
LOCK TABLES a WRITE; INSERT INTO a VALUES (1,23),(2,34),(4,33); INSERT INTO a VALUES (8,26),(6,29); ... UNLOCK TABLES;
This benefits performance because the index buffer is
flushed to disk only once, after all
INSERT statements have
completed. Normally, there would be as many index buffer
flushes as there are INSERT
statements. Explicit locking statements are not needed if
you can insert all rows with a single
INSERT.
Locking also lowers the total time for multiple-connection tests, although the maximum wait time for individual connections might go up because they wait for locks. Suppose that five clients attempt to perform inserts simultaneously as follows:
Connection 1 does 1000 inserts
Connections 2, 3, and 4 do 1 insert
Connection 5 does 1000 inserts
If you do not use locking, connections 2, 3, and 4 finish before 1 and 5. If you use locking, connections 2, 3, and 4 probably do not finish before 1 or 5, but the total time should be about 40% faster.
INSERT,
UPDATE, and
DELETE operations are very
fast in MySQL, but you can obtain better overall performance
by adding locks around everything that does more than about
five successive inserts or updates. If you do very many
successive inserts, you could do a LOCK
TABLES followed by an
UNLOCK
TABLES once in a while (each 1,000 rows or so) to
permit other threads to access table. This would still
result in a nice performance gain.
INSERT is still much slower
for loading data than
LOAD DATA
INFILE, even when using the strategies just
outlined.
To increase performance for MyISAM
tables, for both
LOAD DATA
INFILE and INSERT,
enlarge the key cache by increasing the
key_buffer_size system
variable. See Section 8.12.2, “Tuning Server Parameters”.
REPAIR TABLE for
MyISAM tables is similar to using
myisamchk for repair operations, and some of
the same performance optimizations apply:
myisamchk has variables that control memory allocation. You may be able to its improve performance by setting these variables, as described in Section 4.6.3.6, “myisamchk Memory Usage”.
For REPAIR TABLE, the same
principle applies, but because the repair is done by the
server, you set server system variables instead of
myisamchk variables. Also, in addition to
setting memory-allocation variables, increasing the
myisam_max_sort_file_size
system variable increases the likelihood that the repair
will use the faster filesort method and avoid the slower
repair by key cache method. Set the variable to the maximum
file size for your system, after checking to be sure that
there is enough free space to hold a copy of the table
files. The free space must be available in the file system
containing the original table files.
Suppose that a myisamchk table-repair operation is done using the following options to set its memory-allocation variables:
--key_buffer_size=128M --myisam_sort_buffer_size=256M --read_buffer_size=64M --write_buffer_size=64M
Some of those myisamchk variables correspond to server system variables:
| myisamchk Variable | System Variable |
|---|---|
key_buffer_size | key_buffer_size |
myisam_sort_buffer_size | myisam_sort_buffer_size |
read_buffer_size | read_buffer_size |
write_buffer_size | none |
Each of the server system variables can be set at runtime, and
some of them
(myisam_sort_buffer_size,
read_buffer_size) have a
session value in addition to a global value. Setting a session
value limits the effect of the change to your current session
and does not affect other users. Changing a global-only variable
(key_buffer_size,
myisam_max_sort_file_size)
affects other users as well. For
key_buffer_size, you must take
into account that the buffer is shared with those users. For
example, if you set the myisamchk
key_buffer_size variable to 128MB, you could
set the corresponding
key_buffer_size system variable
larger than that (if it is not already set larger), to permit
key buffer use by activity in other sessions. However, changing
the global key buffer size invalidates the buffer, causing
increased disk I/O and slowdown for other sessions. An
alternative that avoids this problem is to use a separate key
cache, assign to it the indexes from the table to be repaired,
and deallocate it when the repair is complete. See
Section 8.10.2.2, “Multiple Key Caches”.
Based on the preceding remarks, a REPAIR
TABLE operation can be done as follows to use settings
similar to the myisamchk command. Here a
separate 128MB key buffer is allocated and the file system is
assumed to permit a file size of at least 100GB.
SET SESSION myisam_sort_buffer_size = 256*1024*1024; SET SESSION read_buffer_size = 64*1024*1024; SET GLOBAL myisam_max_sort_file_size = 100*1024*1024*1024; SET GLOBAL repair_cache.key_buffer_size = 128*1024*1024; CACHE INDEXtbl_nameIN repair_cache; LOAD INDEX INTO CACHEtbl_name; REPAIR TABLEtbl_name; SET GLOBAL repair_cache.key_buffer_size = 0;
If you intend to change a global variable but want to do so only
for the duration of a REPAIR
TABLE operation to minimally affect other users, save
its value in a user variable and restore it afterward. For
example:
SET @old_myisam_sort_buffer_size = @@global.myisam_max_sort_file_size; SET GLOBAL myisam_max_sort_file_size = 100*1024*1024*1024; REPAIR TABLE tbl_name ; SET GLOBAL myisam_max_sort_file_size = @old_myisam_max_sort_file_size;
The system variables that affect REPAIR
TABLE can be set globally at server startup if you
want the values to be in effect by default. For example, add
these lines to the server my.cnf file:
[mysqld] myisam_sort_buffer_size=256M key_buffer_size=1G myisam_max_sort_file_size=100G
These settings do not include
read_buffer_size. Setting
read_buffer_size globally to a
large value does so for all sessions and can cause performance
to suffer due to excessive memory allocation for a server with
many simultaneous sessions.
Consider using MEMORY tables for noncritical
data that is accessed often, and is read-only or rarely updated.
Benchmark your application against equivalent
InnoDB or MyISAM tables
under a realistic workload, to confirm that any additional
performance is worth the risk of losing data, or the overhead of
copying data from a disk-based table at application start.
For best performance with MEMORY tables,
examine the kinds of queries against each table, and specify the
type to use for each associated index, either a B-tree index or a
hash index. On the CREATE INDEX
statement, use the clause USING BTREE or
USING HASH. B-tree indexes are fast for queries
that do greater-than or less-than comparisons through operators
such as > or BETWEEN.
Hash indexes are only fast for queries that look up single values
through the = operator, or a restricted set of
values through the IN operator. For why
USING BTREE is often a better choice than the
default USING HASH, see
Section 8.2.1.17, “How to Avoid Full Table Scans”. For implementation
details of the different types of MEMORY
indexes, see Section 8.3.8, “Comparison of B-Tree and Hash Indexes”.
Depending on the details of your tables, columns, indexes, and the
conditions in your WHERE clause, the MySQL
optimizer considers many techniques to efficiently perform the
lookups involved in an SQL query. A query on a huge table can be
performed without reading all the rows; a join involving several
tables can be performed without comparing every combination of
rows. The set of operations that the optimizer chooses to perform
the most efficient query is called the “query execution
plan”, also known as the
EXPLAIN plan. Your goals are to
recognize the aspects of the
EXPLAIN plan that indicate a query
is optimized well, and to learn the SQL syntax and indexing
techniques to improve the plan if you see some inefficient
operations.
The EXPLAIN statement can be used
to obtain information about how MySQL executes a statement:
When you precede a SELECT
statement with the keyword
EXPLAIN, MySQL displays
information from the optimizer about the statement execution
plan. That is, MySQL explains how it would process the
statement, including information about how tables are joined
and in which order. For information about using
EXPLAIN to obtain execution
plan information, see Section 8.8.2, “EXPLAIN Output Format”.
EXPLAIN EXTENDED can be used
to obtain additional execution plan information. See
Section 8.8.3, “EXPLAIN EXTENDED Output Format”.
EXPLAIN
PARTITIONS is useful for examining queries
involving partitioned tables. See
Section 19.3.4, “Obtaining Information About Partitions”.
With the help of EXPLAIN, you can
see where you should add indexes to tables so that the statement
executes faster by using indexes to find rows. You can also use
EXPLAIN to check whether the
optimizer joins the tables in an optimal order. To give a hint
to the optimizer to use a join order corresponding to the order
in which the tables are named in a
SELECT statement, begin the
statement with SELECT STRAIGHT_JOIN rather
than just SELECT. (See
Section 13.2.9, “SELECT Syntax”.)
If you have a problem with indexes not being used when you
believe that they should be, run ANALYZE
TABLE to update table statistics, such as cardinality
of keys, that can affect the choices the optimizer makes. See
Section 13.7.2.1, “ANALYZE TABLE Syntax”.
EXPLAIN can also be used to
obtain information about the columns in a table.
EXPLAIN
is synonymous
with tbl_nameDESCRIBE
and
tbl_nameSHOW COLUMNS FROM
. For more
information, see Section 13.8.1, “DESCRIBE Syntax”, and
Section 13.7.5.6, “SHOW COLUMNS Syntax”.
tbl_name
The EXPLAIN statement provides
information about the execution plan for a
SELECT statement.
EXPLAIN returns a row of
information for each table used in the
SELECT statement. It lists the
tables in the output in the order that MySQL would read them
while processing the statement. MySQL resolves all joins using a
nested-loop join method. This means that MySQL reads a row from
the first table, and then finds a matching row in the second
table, the third table, and so on. When all tables are
processed, MySQL outputs the selected columns and backtracks
through the table list until a table is found for which there
are more matching rows. The next row is read from this table and
the process continues with the next table.
When the EXTENDED keyword is used,
EXPLAIN produces extra
information that can be viewed by issuing a
SHOW WARNINGS statement following
the EXPLAIN statement.
EXPLAIN EXTENDED also displays
the filtered column. See
Section 8.8.3, “EXPLAIN EXTENDED Output Format”.
You cannot use the EXTENDED and
PARTITIONS keywords together in the same
EXPLAIN statement.
This section describes the output columns produced by
EXPLAIN. Later sections provide
additional information about the
type
and
Extra
columns.
Each output row from EXPLAIN
provides information about one table. Each row contains the
values summarized in
Table 8.1, “EXPLAIN Output Columns”, and described in
more detail following the table.
Table 8.1 EXPLAIN Output Columns
| Column | Meaning |
|---|---|
id | The SELECT identifier |
select_type | The SELECT type |
table | The table for the output row |
partitions | The matching partitions |
type | The join type |
possible_keys | The possible indexes to choose |
key | The index actually chosen |
key_len | The length of the chosen key |
ref | The columns compared to the index |
rows | Estimate of rows to be examined |
filtered | Percentage of rows filtered by table condition |
Extra | Additional information |
The SELECT identifier. This
is the sequential number of the
SELECT within the query. The
value can be NULL if the row refers to
the union result of other rows. In this case, the
table column shows a value like
<union
to indicate that the row refers to the union of the rows
with M,N>id values of
M and
N.
The type of SELECT, which can
be any of those shown in the following table.
select_type Value | Meaning |
|---|---|
SIMPLE | Simple SELECT (not using
UNION or subqueries) |
PRIMARY | Outermost SELECT |
UNION | Second or later SELECT statement in a
UNION |
DEPENDENT UNION | Second or later SELECT statement in a
UNION, dependent on
outer query |
UNION RESULT | Result of a UNION. |
SUBQUERY | First SELECT in subquery |
DEPENDENT SUBQUERY | First SELECT in subquery, dependent on
outer query |
DERIVED | Derived table SELECT (subquery in
FROM clause) |
UNCACHEABLE SUBQUERY | A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query |
UNCACHEABLE UNION | The second or later select in a UNION
that belongs to an uncacheable subquery (see
UNCACHEABLE SUBQUERY) |
DEPENDENT typically signifies the use of
a correlated subquery. See
Section 13.2.10.7, “Correlated Subqueries”.
DEPENDENT SUBQUERY evaluation differs
from UNCACHEABLE SUBQUERY evaluation. For
DEPENDENT SUBQUERY, the subquery is
re-evaluated only once for each set of different values of
the variables from its outer context. For
UNCACHEABLE SUBQUERY, the subquery is
re-evaluated for each row of the outer context.
Cacheability of subqueries differs from caching of query results in the query cache (which is described in Section 8.10.3.1, “How the Query Cache Operates”). Subquery caching occurs during query execution, whereas the query cache is used to store results only after query execution finishes.
The name of the table to which the row of output refers. This can also be one of the following values:
<union:
The row refers to the union of the rows with
M,N>id values of
M and
N.
<derived:
The row refers to the derived table result for the row
with an N>id value of
N. A derived table may
result, for example, from a subquery in the
FROM clause.
The partitions from which records would be matched by the
query. This column is displayed only if the
PARTITIONS keyword is used. The value is
NULL for nonpartitioned tables. See
Section 19.3.4, “Obtaining Information About Partitions”.
The join type. For descriptions of the different types, see
EXPLAIN
Join Types.
The possible_keys column indicates which
indexes MySQL can choose from use to find the rows in this
table. Note that this column is totally independent of the
order of the tables as displayed in the output from
EXPLAIN. That means that some
of the keys in possible_keys might not be
usable in practice with the generated table order.
If this column is NULL, there are no
relevant indexes. In this case, you may be able to improve
the performance of your query by examining the
WHERE clause to check whether it refers
to some column or columns that would be suitable for
indexing. If so, create an appropriate index and check the
query with EXPLAIN again. See
Section 13.1.7, “ALTER TABLE Syntax”.
To see what indexes a table has, use SHOW INDEX
FROM .
tbl_name
The key column indicates the key (index)
that MySQL actually decided to use. If MySQL decides to use
one of the possible_keys indexes to look
up rows, that index is listed as the key value.
It is possible that key will name an
index that is not present in the
possible_keys value. This can happen if
none of the possible_keys indexes are
suitable for looking up rows, but all the columns selected
by the query are columns of some other index. That is, the
named index covers the selected columns, so although it is
not used to determine which rows to retrieve, an index scan
is more efficient than a data row scan.
For InnoDB, a secondary index might cover
the selected columns even if the query also selects the
primary key because InnoDB stores the
primary key value with each secondary index. If
key is NULL, MySQL
found no index to use for executing the query more
efficiently.
To force MySQL to use or ignore an index listed in the
possible_keys column, use FORCE
INDEX, USE INDEX, or
IGNORE INDEX in your query. See
Section 8.9.3, “Index Hints”.
For MyISAM and NDB
tables, running ANALYZE TABLE
helps the optimizer choose better indexes. For
NDB tables, this also improves
performance of distributed pushed-down joins. For
MyISAM tables, myisamchk
--analyze does the same as
ANALYZE TABLE. See
Section 7.6, “MyISAM Table Maintenance and Crash Recovery”.
The key_len column indicates the length
of the key that MySQL decided to use. The length is
NULL if the key column
says NULL. Note that the value of
key_len enables you to determine how many
parts of a multiple-part key MySQL actually uses.
The ref column shows which columns or
constants are compared to the index named in the
key column to select rows from the table.
The rows column indicates the number of
rows MySQL believes it must examine to execute the query.
For InnoDB tables, this number
is an estimate, and may not always be exact.
The filtered column indicates an
estimated percentage of table rows that will be filtered by
the table condition. That is, rows shows
the estimated number of rows examined and
rows × filtered
/ 100 shows the number of rows that will
be joined with previous tables. This column is displayed if
you use EXPLAIN EXTENDED.
This column contains additional information about how MySQL
resolves the query. For descriptions of the different
values, see
EXPLAIN
Extra Information.
The type column of
EXPLAIN output describes how
tables are joined. The following list describes the join types,
ordered from the best type to the worst:
The table has only one row (= system table). This is a
special case of the const
join type.
The table has at most one matching row, which is read at the
start of the query. Because there is only one row, values
from the column in this row can be regarded as constants by
the rest of the optimizer.
const tables are very
fast because they are read only once.
const is used when you
compare all parts of a PRIMARY KEY or
UNIQUE index to constant values. In the
following queries, tbl_name can
be used as a const table:
SELECT * FROMtbl_nameWHEREprimary_key=1; SELECT * FROMtbl_nameWHEREprimary_key_part1=1 ANDprimary_key_part2=2;
One row is read from this table for each combination of rows
from the previous tables. Other than the
system and
const types, this is the
best possible join type. It is used when all parts of an
index are used by the join and the index is a
PRIMARY KEY or UNIQUE NOT
NULL index.
eq_ref can be used for
indexed columns that are compared using the
= operator. The comparison value can be a
constant or an expression that uses columns from tables that
are read before this table. In the following examples, MySQL
can use an eq_ref join to
process ref_table:
SELECT * FROMref_table,other_tableWHEREref_table.key_column=other_table.column; SELECT * FROMref_table,other_tableWHEREref_table.key_column_part1=other_table.columnANDref_table.key_column_part2=1;
All rows with matching index values are read from this table
for each combination of rows from the previous tables.
ref is used if the join
uses only a leftmost prefix of the key or if the key is not
a PRIMARY KEY or
UNIQUE index (in other words, if the join
cannot select a single row based on the key value). If the
key that is used matches only a few rows, this is a good
join type.
ref can be used for
indexed columns that are compared using the
= or <=>
operator. In the following examples, MySQL can use a
ref join to process
ref_table:
SELECT * FROMref_tableWHEREkey_column=expr; SELECT * FROMref_table,other_tableWHEREref_table.key_column=other_table.column; SELECT * FROMref_table,other_tableWHEREref_table.key_column_part1=other_table.columnANDref_table.key_column_part2=1;
The join is performed using a FULLTEXT
index.
This join type is like
ref, but with the
addition that MySQL does an extra search for rows that
contain NULL values. This join type
optimization is used most often in resolving subqueries. In
the following examples, MySQL can use a
ref_or_null join to
process ref_table:
SELECT * FROMref_tableWHEREkey_column=exprORkey_columnIS NULL;
This join type indicates that the Index Merge optimization
is used. In this case, the key column in
the output row contains a list of indexes used, and
key_len contains a list of the longest
key parts for the indexes used. For more information, see
Section 8.2.1.4, “Index Merge Optimization”.
This type replaces eq_ref
for some IN subqueries of the following
form:
valueIN (SELECTprimary_keyFROMsingle_tableWHEREsome_expr)
unique_subquery is just
an index lookup function that replaces the subquery
completely for better efficiency.
This join type is similar to
unique_subquery. It
replaces IN subqueries, but it works for
nonunique indexes in subqueries of the following form:
valueIN (SELECTkey_columnFROMsingle_tableWHEREsome_expr)
Only rows that are in a given range are retrieved, using an
index to select the rows. The key column
in the output row indicates which index is used. The
key_len contains the longest key part
that was used. The ref column is
NULL for this type.
range can be used when a
key column is compared to a constant using any of the
=,
<>,
>,
>=,
<,
<=,
IS NULL,
<=>,
BETWEEN, or
IN() operators:
SELECT * FROMtbl_nameWHEREkey_column= 10; SELECT * FROMtbl_nameWHEREkey_columnBETWEEN 10 and 20; SELECT * FROMtbl_nameWHEREkey_columnIN (10,20,30); SELECT * FROMtbl_nameWHEREkey_part1= 10 ANDkey_part2IN (10,20,30);
The index join type is the same as
ALL, except that the
index tree is scanned. This occurs two ways:
If the index is a covering index for the queries and can
be used to satisfy all data required from the table,
only the index tree is scanned. In this case, the
Extra column says Using
index. An index-only scan usually is faster
than ALL because the
size of the index usually is smaller than the table
data.
A full table scan is performed using reads from the
index to look up data rows in index order. Uses
index does not appear in the
Extra column.
MySQL can use this join type when the query uses only columns that are part of a single index.
A full table scan is done for each combination of rows from
the previous tables. This is normally not good if the table
is the first table not marked
const, and usually
very bad in all other cases. Normally,
you can avoid ALL by
adding indexes that enable row retrieval from the table
based on constant values or column values from earlier
tables.
The Extra column of
EXPLAIN output contains
additional information about how MySQL resolves the query. The
following list explains the values that can appear in this
column. If you want to make your queries as fast as possible,
look out for Extra values of Using
filesort and Using temporary.
Child of '
table' pushed
join@1
This table is referenced as the child of
table in a join that can be
pushed down to the NDB kernel. Applies only in MySQL Cluster
NDB 7.2 and later, when pushed-down joins are enabled. See
the description of the
ndb_join_pushdown server
system variable for more information and examples.
const row not found
For a query such as SELECT ... FROM
, the table was
empty.
tbl_name
Distinct
MySQL is looking for distinct values, so it stops searching for more rows for the current row combination after it has found the first matching row.
Full scan on NULL key
This occurs for subquery optimization as a fallback strategy when the optimizer cannot use an index-lookup access method.
Impossible HAVING
The HAVING clause is always false and
cannot select any rows.
Impossible WHERE
The WHERE clause is always false and
cannot select any rows.
Impossible WHERE noticed after reading const
tables
MySQL has read all const
(and system) tables and
notice that the WHERE clause is always
false.
No matching min/max row
No row satisfies the condition for a query such as
SELECT MIN(...) FROM ... WHERE
.
condition
no matching row in const table
For a query with a join, there was an empty table or a table with no rows satisfying a unique index condition.
No tables used
The query has no FROM clause, or has a
FROM DUAL clause.
Not exists
MySQL was able to do a LEFT JOIN
optimization on the query and does not examine more rows in
this table for the previous row combination after it finds
one row that matches the LEFT JOIN
criteria. Here is an example of the type of query that can
be optimized this way:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
Assume that t2.id is defined as
NOT NULL. In this case, MySQL scans
t1 and looks up the rows in
t2 using the values of
t1.id. If MySQL finds a matching row in
t2, it knows that
t2.id can never be
NULL, and does not scan through the rest
of the rows in t2 that have the same
id value. In other words, for each row in
t1, MySQL needs to do only a single
lookup in t2, regardless of how many rows
actually match in t2.
Range checked for each record (index map:
N)
MySQL found no good index to use, but found that some of
indexes might be used after column values from preceding
tables are known. For each row combination in the preceding
tables, MySQL checks whether it is possible to use a
range or
index_merge access method
to retrieve rows. This is not very fast, but is faster than
performing a join with no index at all. The applicability
criteria are as described in
Section 8.2.1.3, “Range Optimization”, and
Section 8.2.1.4, “Index Merge Optimization”, with the
exception that all column values for the preceding table are
known and considered to be constants.
Indexes are numbered beginning with 1, in the same order as
shown by SHOW INDEX for the
table. The index map value N is a
bitmask value that indicates which indexes are candidates.
For example, a value of 0x19 (binary
11001) means that indexes 1, 4, and 5 will be considered.
Scanned
N
databases
This indicates how many directory scans the server performs
when processing a query for
INFORMATION_SCHEMA tables, as described
in Section 8.2.4, “Optimizing INFORMATION_SCHEMA Queries”. The
value of N can be 0, 1, or
all.
Select tables optimized away
The optimizer determined 1) that at most one row should be returned, and 2) that to produce this row, a deterministic set of rows must be read. When the rows to be read can be read during the optimization phase (for example, by reading index rows), there is no need to read any tables during query execution.
The first condition is fulfilled when the query is
implicitly grouped (contains an aggregate function but no
GROUP BY clause). The second condition is
fulfilled when one row lookup is performed per index used.
The number of indexes read determines the number of rows to
read.
Consider the following implicitly grouped query:
SELECT MIN(c1), MIN(c2) FROM t1;
Suppose that MIN(c1) can be retrieved by
reading one index row and MIN(c2) can be
retrieved by reading one row from a different index. That
is, for each column c1 and
c2, there exists an index where the
column is the first column of the index. In this case, one
row is returned, produced by reading two deterministic rows.
This Extra value does not occur if the
rows to read are not deterministic. Consider this query:
SELECT MIN(c2) FROM t1 WHERE c1 <= 10;
Suppose that (c1, c2) is a covering
index. Using this index, all rows with c1 <=
10 must be scanned to find the minimum
c2 value. By contrast, consider this
query:
SELECT MIN(c2) FROM t1 WHERE c1 = 10;
In this case, the first index row with c1 =
10 contains the minimum c2
value. Only one row must be read to produce the returned
row.
For storage engines that maintain an exact row count per
table (such as MyISAM, but not
InnoDB), this Extra
value can occur for COUNT(*) queries for
which the WHERE clause is missing or
always true and there is no GROUP BY
clause. (This is an instance of an implicitly grouped query
where the storage engine influences whether a deterministic
number of rows can be read.)
Skip_open_table,
Open_frm_only,
Open_trigger_only,
Open_full_table
These values indicate file-opening optimizations that apply
to queries for INFORMATION_SCHEMA tables,
as described in
Section 8.2.4, “Optimizing INFORMATION_SCHEMA Queries”.
Skip_open_table: Table files do not
need to be opened. The information has already become
available within the query by scanning the database
directory.
Open_frm_only: Only the table's
.frm file need be opened.
Open_trigger_only: Only the table's
.TRG file need be opened.
Open_full_table: The unoptimized
information lookup. The .frm,
.MYD, and .MYI
files must be opened.
unique row not found
For a query such as SELECT ... FROM
, no rows
satisfy the condition for a tbl_nameUNIQUE index
or PRIMARY KEY on the table.
Using filesort
MySQL must do an extra pass to find out how to retrieve the
rows in sorted order. The sort is done by going through all
rows according to the join type and storing the sort key and
pointer to the row for all rows that match the
WHERE clause. The keys then are sorted
and the rows are retrieved in sorted order. See
Section 8.2.1.11, “ORDER BY Optimization”.
Using index
The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.
For InnoDB tables that have a
user-defined clustered index, that index can be used even
when Using index is absent from the
Extra column. This is the case if
type is
index and
key is PRIMARY.
Using index for group-by
Similar to the Using index table access
method, Using index for group-by
indicates that MySQL found an index that can be used to
retrieve all columns of a GROUP BY or
DISTINCT query without any extra disk
access to the actual table. Additionally, the index is used
in the most efficient way so that for each group, only a few
index entries are read. For details, see
Section 8.2.1.12, “GROUP BY Optimization”.
Using join buffer
Tables from earlier joins are read in portions into the join buffer, and then their rows are used from the buffer to perform the join with the current table.
Using sort_union(...), Using
union(...), Using
intersect(...)
These indicate how index scans are merged for the
index_merge join type.
See Section 8.2.1.4, “Index Merge Optimization”.
Using temporary
To resolve the query, MySQL needs to create a temporary
table to hold the result. This typically happens if the
query contains GROUP BY and
ORDER BY clauses that list columns
differently.
Using where
A WHERE clause is used to restrict which
rows to match against the next table or send to the client.
Unless you specifically intend to fetch or examine all rows
from the table, you may have something wrong in your query
if the Extra value is not Using
where and the table join type is
ALL or
index. Even if you are
using an index for all parts of a WHERE
clause, you may see Using where if the
column can be NULL.
Using where with pushed condition
This item applies to NDB tables
only. It means that MySQL Cluster is
using the Condition Pushdown optimization to improve the
efficiency of a direct comparison between a nonindexed
column and a constant. In such cases, the condition is
“pushed down” to the cluster's data nodes and
is evaluated on all data nodes simultaneously. This
eliminates the need to send nonmatching rows over the
network, and can speed up such queries by a factor of 5 to
10 times over cases where Condition Pushdown could be but is
not used. For more information, see
Section 8.2.1.5, “Engine Condition Pushdown Optimization”.
You can get a good indication of how good a join is by taking
the product of the values in the rows column
of the EXPLAIN output. This
should tell you roughly how many rows MySQL must examine to
execute the query. If you restrict queries with the
max_join_size system variable,
this row product also is used to determine which multiple-table
SELECT statements to execute and
which to abort. See Section 8.12.2, “Tuning Server Parameters”.
The following example shows how a multiple-table join can be
optimized progressively based on the information provided by
EXPLAIN.
Suppose that you have the SELECT
statement shown here and that you plan to examine it using
EXPLAIN:
EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
tt.ProjectReference, tt.EstimatedShipDate,
tt.ActualShipDate, tt.ClientID,
tt.ServiceCodes, tt.RepetitiveID,
tt.CurrentProcess, tt.CurrentDPPerson,
tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
et_1.COUNTRY, do.CUSTNAME
FROM tt, et, et AS et_1, do
WHERE tt.SubmitTime IS NULL
AND tt.ActualPC = et.EMPLOYID
AND tt.AssignedPC = et_1.EMPLOYID
AND tt.ClientID = do.CUSTNMBR;
For this example, make the following assumptions:
The columns being compared have been declared as follows.
| Table | Column | Data Type |
|---|---|---|
tt | ActualPC | CHAR(10) |
tt | AssignedPC | CHAR(10) |
tt | ClientID | CHAR(10) |
et | EMPLOYID | CHAR(15) |
do | CUSTNMBR | CHAR(15) |
The tables have the following indexes.
| Table | Index |
|---|---|
tt | ActualPC |
tt | AssignedPC |
tt | ClientID |
et | EMPLOYID (primary key) |
do | CUSTNMBR (primary key) |
The tt.ActualPC values are not evenly
distributed.
Initially, before any optimizations have been performed, the
EXPLAIN statement produces the
following information:
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
do ALL PRIMARY NULL NULL NULL 2135
et_1 ALL PRIMARY NULL NULL NULL 74
tt ALL AssignedPC, NULL NULL NULL 3872
ClientID,
ActualPC
Range checked for each record (index map: 0x23)
Because type is
ALL for each table, this
output indicates that MySQL is generating a Cartesian product of
all the tables; that is, every combination of rows. This takes
quite a long time, because the product of the number of rows in
each table must be examined. For the case at hand, this product
is 74 × 2135 × 74 × 3872 = 45,268,558,720
rows. If the tables were bigger, you can only imagine how long
it would take.
One problem here is that MySQL can use indexes on columns more
efficiently if they are declared as the same type and size. In
this context, VARCHAR and
CHAR are considered the same if
they are declared as the same size.
tt.ActualPC is declared as
CHAR(10) and et.EMPLOYID
is CHAR(15), so there is a length mismatch.
To fix this disparity between column lengths, use
ALTER TABLE to lengthen
ActualPC from 10 characters to 15 characters:
mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
Now tt.ActualPC and
et.EMPLOYID are both
VARCHAR(15). Executing the
EXPLAIN statement again produces
this result:
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC, NULL NULL NULL 3872 Using
ClientID, where
ActualPC
do ALL PRIMARY NULL NULL NULL 2135
Range checked for each record (index map: 0x1)
et_1 ALL PRIMARY NULL NULL NULL 74
Range checked for each record (index map: 0x1)
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
This is not perfect, but is much better: The product of the
rows values is less by a factor of 74. This
version executes in a couple of seconds.
A second alteration can be made to eliminate the column length
mismatches for the tt.AssignedPC =
et_1.EMPLOYID and tt.ClientID =
do.CUSTNMBR comparisons:
mysql>ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),->MODIFY ClientID VARCHAR(15);
After that modification, EXPLAIN
produces the output shown here:
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using
ClientID, where
ActualPC
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
At this point, the query is optimized almost as well as
possible. The remaining problem is that, by default, MySQL
assumes that values in the tt.ActualPC column
are evenly distributed, and that is not the case for the
tt table. Fortunately, it is easy to tell
MySQL to analyze the key distribution:
mysql> ANALYZE TABLE tt;
With the additional index information, the join is perfect and
EXPLAIN produces this result:
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC NULL NULL NULL 3872 Using
ClientID, where
ActualPC
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
The rows column in the output from
EXPLAIN is an educated guess from
the MySQL join optimizer. Check whether the numbers are even
close to the truth by comparing the rows
product with the actual number of rows that the query returns.
If the numbers are quite different, you might get better
performance by using STRAIGHT_JOIN in your
SELECT statement and trying to
list the tables in a different order in the
FROM clause.
It is possible in some cases to execute statements that modify
data when EXPLAIN
SELECT is used with a subquery; for more information,
see Section 13.2.10.8, “Subqueries in the FROM Clause”.
When EXPLAIN is used with the
EXTENDED keyword, the output includes a
filtered column not otherwise displayed. This
column indicates the estimated percentage of table rows that
will be filtered by the table condition. In addition, the
statement produces extra information that can be viewed by
issuing a SHOW WARNINGS statement
following the EXPLAIN statement.
The Message value in
SHOW WARNINGS output displays how
the optimizer qualifies table and column names in the
SELECT statement, what the
SELECT looks like after the
application of rewriting and optimization rules, and possibly
other notes about the optimization process.
Here is an example of extended output:
mysql>EXPLAIN EXTENDED->SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1\G*************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 4 filtered: 100.00 Extra: Using index *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: t2 type: index_subquery possible_keys: a key: a key_len: 5 ref: func rows: 2 filtered: 100.00 Extra: Using index 2 rows in set, 1 warning (0.00 sec) mysql>SHOW WARNINGS\G*************************** 1. row *************************** Level: Note Code: 1003 Message: select `test`.`t1`.`a` AS `a`, <in_optimizer>(`test`.`t1`.`a`, <exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on a checking NULL having <is_not_null_test>(`test`.`t2`.`a`)))) AS `t1.a IN (SELECT t2.a FROM t2)` from `test`.`t1` 1 row in set (0.00 sec)
Because the statement displayed by SHOW
WARNINGS may contain special markers to provide
information about query rewriting or optimizer actions, the
statement is not necessarily valid SQL and is not intended to be
executed. The output may also include rows with
Message values that provide additional
non-SQL explanatory notes about actions taken by the optimizer.
The following list describes special markers that can appear in
EXTENDED output displayed by
SHOW WARNINGS:
<cache>(
expr)
The expression (such as a scalar subquery) is executed once and the resulting value is saved in memory for later use.
<exists>(
query
fragment)
The subquery predicate is converted to an
EXISTS predicate and the subquery is
transformed so that it can be used together with the
EXISTS predicate.
<in_optimizer>(
query
fragment)
This is an internal optimizer object with no user significance.
<index_lookup>(
query
fragment)
The query fragment is processed using an index lookup to find qualifying rows.
<is_not_null_test>(
expr)
A test to verify that the expression does not evaluate to
NULL.
<primary_index_lookup>(
query
fragment)
The query fragment is processed using a primary key lookup to find qualifying rows.
<ref_null_helper>(
expr)
This is an internal optimizer object with no user significance.
When some tables are of const
or system type, expressions
involving columns from these tables are evaluated early by the
optimizer and are not part of the displayed statement. However,
with FORMAT=JSON, some
const table accesses are
displayed as a ref access
that uses a const value.
In most cases, you can estimate query performance by counting
disk seeks. For small tables, you can usually find a row in one
disk seek (because the index is probably cached). For bigger
tables, you can estimate that, using B-tree indexes, you need
this many seeks to find a row:
log(.
row_count) /
log(index_block_length / 3 * 2 /
(index_length +
data_pointer_length)) + 1
In MySQL, an index block is usually 1,024 bytes and the data
pointer is usually four bytes. For a 500,000-row table with a
key value length of three bytes (the size of
MEDIUMINT), the formula indicates
log(500,000)/log(1024/3*2/(3+4)) + 1 =
4 seeks.
This index would require storage of about 500,000 * 7 * 3/2 = 5.2MB (assuming a typical index buffer fill ratio of 2/3), so you probably have much of the index in memory and so need only one or two calls to read data to find the row.
For writes, however, you need four seek requests to find where to place a new index value and normally two seeks to update the index and write the row.
The preceding discussion does not mean that your application
performance slowly degenerates by log
N. As long as everything is cached by
the OS or the MySQL server, things become only marginally slower
as the table gets bigger. After the data gets too big to be
cached, things start to go much slower until your applications
are bound only by disk seeks (which increase by log
N). To avoid this, increase the key
cache size as the data grows. For MyISAM
tables, the key cache size is controlled by the
key_buffer_size system
variable. See Section 8.12.2, “Tuning Server Parameters”.
MySQL provides optimizer control through system variables that affect how query plans are evaluated, switchable optimizations, and index hints.
The task of the query optimizer is to find an optimal plan for executing an SQL query. Because the difference in performance between “good” and “bad” plans can be orders of magnitude (that is, seconds versus hours or even days), most query optimizers, including that of MySQL, perform a more or less exhaustive search for an optimal plan among all possible query evaluation plans. For join queries, the number of possible plans investigated by the MySQL optimizer grows exponentially with the number of tables referenced in a query. For small numbers of tables (typically less than 7 to 10) this is not a problem. However, when larger queries are submitted, the time spent in query optimization may easily become the major bottleneck in the server's performance.
A more flexible method for query optimization enables the user to control how exhaustive the optimizer is in its search for an optimal query evaluation plan. The general idea is that the fewer plans that are investigated by the optimizer, the less time it spends in compiling a query. On the other hand, because the optimizer skips some plans, it may miss finding an optimal plan.
The behavior of the optimizer with respect to the number of plans it evaluates can be controlled using two system variables:
The optimizer_prune_level
variable tells the optimizer to skip certain plans based on
estimates of the number of rows accessed for each table. Our
experience shows that this kind of “educated
guess” rarely misses optimal plans, and may
dramatically reduce query compilation times. That is why
this option is on
(optimizer_prune_level=1) by default.
However, if you believe that the optimizer missed a better
query plan, this option can be switched off
(optimizer_prune_level=0) with the risk
that query compilation may take much longer. Note that, even
with the use of this heuristic, the optimizer still explores
a roughly exponential number of plans.
The optimizer_search_depth
variable tells how far into the “future” of
each incomplete plan the optimizer should look to evaluate
whether it should be expanded further. Smaller values of
optimizer_search_depth may
result in orders of magnitude smaller query compilation
times. For example, queries with 12, 13, or more tables may
easily require hours and even days to compile if
optimizer_search_depth is
close to the number of tables in the query. At the same
time, if compiled with
optimizer_search_depth
equal to 3 or 4, the optimizer may compile in less than a
minute for the same query. If you are unsure of what a
reasonable value is for
optimizer_search_depth,
this variable can be set to 0 to tell the optimizer to
determine the value automatically.
The optimizer_switch system
variable enables control over optimizer behavior. Its value is a
set of flags, each of which has a value of on
or off to indicate whether the corresponding
optimizer behavior is enabled or disabled. This variable has
global and session values and can be changed at runtime. The
global default can be set at server startup.
To see the current set of optimizer flags, select the variable value:
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on
To change the value of
optimizer_switch, assign a
value consisting of a comma-separated list of one or more
commands:
SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';
Each command value should have one of
the forms shown in the following table.
| Command Syntax | Meaning |
|---|---|
default | Reset every optimization to its default value |
| Set the named optimization to its default value |
| Disable the named optimization |
| Enable the named optimization |
The order of the commands in the value does not matter, although
the default command is executed first if
present. Setting an opt_name flag to
default sets it to whichever of
on or off is its default
value. Specifying any given opt_name
more than once in the value is not permitted and causes an
error. Any errors in the value cause the assignment to fail with
an error, leaving the value of
optimizer_switch unchanged.
The following table lists the permissible
opt_name flag names, grouped by
optimization strategy.
| Optimization | Flag Name | Meaning | Default |
|---|---|---|---|
| Engine Condition Pushdown | engine_condition_pushdown | Controls engine condition pushdown | ON |
| Index Merge | index_merge | Controls all Index Merge optimizations | ON |
index_merge_intersection | Controls the Index Merge Intersection Access optimization | ON | |
index_merge_sort_union | Controls the Index Merge Sort-Union Access optimization | ON | |
index_merge_union | Controls the Index Merge Union Access optimization | ON |
The flag for engine condition pushdown was added in MySQL 5.5.3.
For more information about individual optimization strategies, see the following sections:
When you assign a value to
optimizer_switch, flags that
are not mentioned keep their current values. This makes it
possible to enable or disable specific optimizer behaviors in a
single statement without affecting other behaviors. The
statement does not depend on what other optimizer flags exist
and what their values are. Suppose that all Index Merge
optimizations are enabled:
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on
If the server is using the Index Merge Union or Index Merge Sort-Union access methods for certain queries and you want to check whether the optimizer will perform better without them, set the variable value like this:
mysql>SET optimizer_switch='index_merge_union=off,index_merge_sort_union=off';mysql>SELECT @@optimizer_switch\G*************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=off, index_merge_sort_union=off, index_merge_intersection=on, engine_condition_pushdown=on
Index hints give the optimizer information about how to choose
indexes during query processing. Index hints are specified
following a table name. (For the general syntax for specifying
tables in a SELECT statement, see
Section 13.2.9.2, “JOIN Syntax”.) The syntax for referring to an
individual table, including index hints, looks like this:
tbl_name[[AS]alias] [index_hint_list]index_hint_list:index_hint[,index_hint] ...index_hint: USE {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list]) | IGNORE {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] (index_list) | FORCE {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)index_list:index_name[,index_name] ...
The USE INDEX
( hint tells
MySQL to use only one of the named indexes to find rows in the
table. The alternative syntax index_list)IGNORE INDEX
( tells MySQL to
not use some particular index or indexes. These hints are useful
if index_list)EXPLAIN shows that MySQL is
using the wrong index from the list of possible indexes.
The FORCE INDEX hint acts like USE
INDEX (, with
the addition that a table scan is assumed to be
very expensive. In other words, a table
scan is used only if there is no way to use one of the named
indexes to find rows in the table.
index_list)
Each hint requires the names of indexes,
not the names of columns. To refer to a primary key, use the
name PRIMARY. To see the index names for a
table, use SHOW INDEX.
An index_name value need not be a
full index name. It can be an unambiguous prefix of an index
name. If a prefix is ambiguous, an error occurs.
Examples:
SELECT * FROM table1 USE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3; SELECT * FROM table1 IGNORE INDEX (col3_index) WHERE col1=1 AND col2=2 AND col3=3;
The syntax for index hints has the following characteristics:
It is syntactically valid to omit
index_list for USE
INDEX, which means “use no indexes.”
Omitting index_list for
FORCE INDEX or IGNORE
INDEX is a syntax error.
You can specify the scope of an index hint by adding a
FOR clause to the hint. This provides
more fine-grained control over the optimizer's selection of
an execution plan for various phases of query processing. To
affect only the indexes used when MySQL decides how to find
rows in the table and how to process joins, use FOR
JOIN. To influence index usage for sorting or
grouping rows, use FOR ORDER BY or
FOR GROUP BY.
You can specify multiple index hints:
SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;
It is not an error to name the same index in several hints (even within the same hint):
SELECT * FROM t1 USE INDEX (i1) USE INDEX (i1,i1);
However, it is an error to mix USE INDEX
and FORCE INDEX for the same table:
SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);
If an index hint includes no FOR clause, the
scope of the hint is to apply to all parts of the statement. For
example, this hint:
IGNORE INDEX (i1)
is equivalent to this combination of hints:
IGNORE INDEX FOR JOIN (i1) IGNORE INDEX FOR ORDER BY (i1) IGNORE INDEX FOR GROUP BY (i1)
In MySQL 5.0, hint scope with no FOR clause
was to apply only to row retrieval. To cause the server to use
this older behavior when no FOR clause is
present, enable the old system
variable at server startup. Take care about enabling this
variable in a replication setup. With statement-based binary
logging, having different modes for the master and slaves might
lead to replication errors.
When index hints are processed, they are collected in a single
list by type (USE,
FORCE, IGNORE) and by
scope (FOR JOIN, FOR ORDER
BY, FOR GROUP BY). For example:
SELECT * FROM t1 USE INDEX () IGNORE INDEX (i2) USE INDEX (i1) USE INDEX (i2);
is equivalent to:
SELECT * FROM t1 USE INDEX (i1,i2) IGNORE INDEX (i2);
The index hints then are applied for each scope in the following order:
{USE|FORCE} INDEX is applied if present.
(If not, the optimizer-determined set of indexes is used.)
IGNORE INDEX is applied over the result
of the previous step. For example, the following two queries
are equivalent:
SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX (i2) USE INDEX (i2); SELECT * FROM t1 USE INDEX (i1);
For FULLTEXT searches, index hints work as
follows:
For natural language mode searches, index hints are silently
ignored. For example, IGNORE INDEX(i1) is
ignored with no warning and the index is still used.
For boolean mode searches, index hints with FOR
ORDER BY or FOR GROUP BY are
silently ignored. Index hints with FOR
JOIN or no FOR modifier are
honored. In contrast to how hints apply for
non-FULLTEXT searches, the hint is used
for all phases of query execution (finding rows and
retrieval, grouping, and ordering). This is true even if the
hint is given for a non-FULLTEXT index.
For example, the following two queries are equivalent:
SELECT * FROM t USE INDEX (index1) IGNORE INDEX (index1) FOR ORDER BY IGNORE INDEX (index1) FOR GROUP BY WHERE ... IN BOOLEAN MODE ... ; SELECT * FROM t USE INDEX (index1) WHERE ... IN BOOLEAN MODE ... ;
MySQL uses several strategies that cache information in memory buffers to increase performance. Depending on your database architecture, you balance the size and layout of these areas, to provide the most performance benefit without wasting memory or exceeding available memory. When you set up or resize these memory areas, test the resulting performance using the techniques from Section 8.13, “Measuring Performance (Benchmarking)”.
InnoDB maintains a storage area
called the buffer pool
for caching data and indexes in memory. Knowing how the
InnoDB buffer pool works, and taking
advantage of it to keep frequently accessed data in memory, is
an important aspect of MySQL tuning.
For an explanation of the inner workings of the
InnoDB buffer pool, an overview of its LRU
replacement algorithm, and general configuration information,
see Section 14.9.2.1, “The InnoDB Buffer Pool”.
For additional InnoDB buffer pool
configuration and tuning information, see these sections:
To minimize disk I/O, the MyISAM storage
engine exploits a strategy that is used by many database
management systems. It employs a cache mechanism to keep the
most frequently accessed table pages in memory:
For index blocks, a special structure called the key cache (or key buffer) is maintained. The structure contains a number of block buffers where the most-used index blocks are placed.
For data blocks, MySQL uses no special cache. Instead it relies on the native operating system file system cache.
This section first describes the basic operation of the
MyISAM key cache. Then it discusses features
that improve key cache performance and that enable you to better
control cache operation:
Multiple sessions can access the cache concurrently.
You can set up multiple key caches and assign table indexes to specific caches.
To control the size of the key cache, use the
key_buffer_size system
variable. If this variable is set equal to zero, no key cache is
used. The key cache also is not used if the
key_buffer_size value is too
small to allocate the minimal number of block buffers (8).
When the key cache is not operational, index files are accessed using only the native file system buffering provided by the operating system. (In other words, table index blocks are accessed using the same strategy as that employed for table data blocks.)
An index block is a contiguous unit of access to the
MyISAM index files. Usually the size of an
index block is equal to the size of nodes of the index B-tree.
(Indexes are represented on disk using a B-tree data structure.
Nodes at the bottom of the tree are leaf nodes. Nodes above the
leaf nodes are nonleaf nodes.)
All block buffers in a key cache structure are the same size. This size can be equal to, greater than, or less than the size of a table index block. Usually one these two values is a multiple of the other.
When data from any table index block must be accessed, the server first checks whether it is available in some block buffer of the key cache. If it is, the server accesses data in the key cache rather than on disk. That is, it reads from the cache or writes into it rather than reading from or writing to disk. Otherwise, the server chooses a cache block buffer containing a different table index block (or blocks) and replaces the data there by a copy of required table index block. As soon as the new index block is in the cache, the index data can be accessed.
If it happens that a block selected for replacement has been modified, the block is considered “dirty.” In this case, prior to being replaced, its contents are flushed to the table index from which it came.
Usually the server follows an LRU (Least Recently Used) strategy: When choosing a block for replacement, it selects the least recently used index block. To make this choice easier, the key cache module maintains all used blocks in a special list (LRU chain) ordered by time of use. When a block is accessed, it is the most recently used and is placed at the end of the list. When blocks need to be replaced, blocks at the beginning of the list are the least recently used and become the first candidates for eviction.
The InnoDB storage engine also uses an LRU
algorithm, to manage its buffer pool. See
Section 14.9.2.1, “The InnoDB Buffer Pool”.
Threads can access key cache buffers simultaneously, subject to the following conditions:
A buffer that is not being updated can be accessed by multiple sessions.
A buffer that is being updated causes sessions that need to use it to wait until the update is complete.
Multiple sessions can initiate requests that result in cache block replacements, as long as they do not interfere with each other (that is, as long as they need different index blocks, and thus cause different cache blocks to be replaced).
Shared access to the key cache enables the server to improve throughput significantly.
Shared access to the key cache improves performance but does not eliminate contention among sessions entirely. They still compete for control structures that manage access to the key cache buffers. To reduce key cache access contention further, MySQL also provides multiple key caches. This feature enables you to assign different table indexes to different key caches.
Where there are multiple key caches, the server must know
which cache to use when processing queries for a given
MyISAM table. By default, all
MyISAM table indexes are cached in the
default key cache. To assign table indexes to a specific key
cache, use the CACHE INDEX
statement (see Section 13.7.6.2, “CACHE INDEX Syntax”). For example,
the following statement assigns indexes from the tables
t1, t2, and
t3 to the key cache named
hot_cache:
mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status | OK |
| test.t2 | assign_to_keycache | status | OK |
| test.t3 | assign_to_keycache | status | OK |
+---------+--------------------+----------+----------+
The key cache referred to in a CACHE
INDEX statement can be created by setting its size
with a SET
GLOBAL parameter setting statement or by using
server startup options. For example:
mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;
To destroy a key cache, set its size to zero:
mysql> SET GLOBAL keycache1.key_buffer_size=0;
You cannot destroy the default key cache. Any attempt to do this is ignored:
mysql>SET GLOBAL key_buffer_size = 0;mysql>SHOW VARIABLES LIKE 'key_buffer_size';+-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | key_buffer_size | 8384512 | +-----------------+---------+
Key cache variables are structured system variables that have
a name and components. For
keycache1.key_buffer_size,
keycache1 is the cache variable name and
key_buffer_size is the cache
component. See Section 5.1.5.1, “Structured System Variables”,
for a description of the syntax used for referring to
structured key cache system variables.
By default, table indexes are assigned to the main (default) key cache created at the server startup. When a key cache is destroyed, all indexes assigned to it are reassigned to the default key cache.
For a busy server, you can use a strategy that involves three key caches:
A “hot” key cache that takes up 20% of the space allocated for all key caches. Use this for tables that are heavily used for searches but that are not updated.
A “cold” key cache that takes up 20% of the space allocated for all key caches. Use this cache for medium-sized, intensively modified tables, such as temporary tables.
A “warm” key cache that takes up 60% of the key cache space. Employ this as the default key cache, to be used by default for all other tables.
One reason the use of three key caches is beneficial is that access to one key cache structure does not block access to the others. Statements that access tables assigned to one cache do not compete with statements that access tables assigned to another cache. Performance gains occur for other reasons as well:
The hot cache is used only for retrieval queries, so its contents are never modified. Consequently, whenever an index block needs to be pulled in from disk, the contents of the cache block chosen for replacement need not be flushed first.
For an index assigned to the hot cache, if there are no queries requiring an index scan, there is a high probability that the index blocks corresponding to nonleaf nodes of the index B-tree remain in the cache.
An update operation most frequently executed for temporary tables is performed much faster when the updated node is in the cache and need not be read in from disk first. If the size of the indexes of the temporary tables are comparable with the size of cold key cache, the probability is very high that the updated node is in the cache.
The CACHE INDEX statement sets
up an association between a table and a key cache, but the
association is lost each time the server restarts. If you want
the association to take effect each time the server starts,
one way to accomplish this is to use an option file: Include
variable settings that configure your key caches, and an
init-file option that names a file
containing CACHE INDEX
statements to be executed. For example:
key_buffer_size = 4G hot_cache.key_buffer_size = 2G cold_cache.key_buffer_size = 2G init_file=/path/to/data-directory/mysqld_init.sql
The statements in mysqld_init.sql are
executed each time the server starts. The file should contain
one SQL statement per line. The following example assigns
several tables each to hot_cache and
cold_cache:
CACHE INDEX db1.t1, db1.t2, db2.t3 IN hot_cache CACHE INDEX db1.t4, db2.t5, db2.t6 IN cold_cache
By default, the key cache management system uses a simple LRU strategy for choosing key cache blocks to be evicted, but it also supports a more sophisticated method called the midpoint insertion strategy.
When using the midpoint insertion strategy, the LRU chain is
divided into two parts: a hot sublist and a warm sublist. The
division point between two parts is not fixed, but the key
cache management system takes care that the warm part is not
“too short,” always containing at least
key_cache_division_limit
percent of the key cache blocks.
key_cache_division_limit is a
component of structured key cache variables, so its value is a
parameter that can be set per cache.
When an index block is read from a table into the key cache, it is placed at the end of the warm sublist. After a certain number of hits (accesses of the block), it is promoted to the hot sublist. At present, the number of hits required to promote a block (3) is the same for all index blocks.
A block promoted into the hot sublist is placed at the end of
the list. The block then circulates within this sublist. If
the block stays at the beginning of the sublist for a long
enough time, it is demoted to the warm sublist. This time is
determined by the value of the
key_cache_age_threshold
component of the key cache.
The threshold value prescribes that, for a key cache
containing N blocks, the block at
the beginning of the hot sublist not accessed within the last
N *
key_cache_age_threshold / 100
The midpoint insertion strategy enables you to keep
more-valued blocks always in the cache. If you prefer to use
the plain LRU strategy, leave the
key_cache_division_limit
value set to its default of 100.
The midpoint insertion strategy helps to improve performance
when execution of a query that requires an index scan
effectively pushes out of the cache all the index blocks
corresponding to valuable high-level B-tree nodes. To avoid
this, you must use a midpoint insertion strategy with the
key_cache_division_limit set
to much less than 100. Then valuable frequently hit nodes are
preserved in the hot sublist during an index scan operation as
well.
If there are enough blocks in a key cache to hold blocks of an entire index, or at least the blocks corresponding to its nonleaf nodes, it makes sense to preload the key cache with index blocks before starting to use it. Preloading enables you to put the table index blocks into a key cache buffer in the most efficient way: by reading the index blocks from disk sequentially.
Without preloading, the blocks are still placed into the key cache as needed by queries. Although the blocks will stay in the cache, because there are enough buffers for all of them, they are fetched from disk in random order, and not sequentially.
To preload an index into a cache, use the
LOAD INDEX INTO
CACHE statement. For example, the following
statement preloads nodes (index blocks) of indexes of the
tables t1 and t2:
mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;
+---------+--------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+--------------+----------+----------+
| test.t1 | preload_keys | status | OK |
| test.t2 | preload_keys | status | OK |
+---------+--------------+----------+----------+
The IGNORE LEAVES modifier causes only
blocks for the nonleaf nodes of the index to be preloaded.
Thus, the statement shown preloads all index blocks from
t1, but only blocks for the nonleaf nodes
from t2.
If an index has been assigned to a key cache using a
CACHE INDEX statement,
preloading places index blocks into that cache. Otherwise, the
index is loaded into the default key cache.
It is possible to specify the size of the block buffers for an
individual key cache using the
key_cache_block_size
variable. This permits tuning of the performance of I/O
operations for index files.
The best performance for I/O operations is achieved when the size of read buffers is equal to the size of the native operating system I/O buffers. But setting the size of key nodes equal to the size of the I/O buffer does not always ensure the best overall performance. When reading the big leaf nodes, the server pulls in a lot of unnecessary data, effectively preventing reading other leaf nodes.
To control the size of blocks in the .MYI
index file of MyISAM tables, use the
--myisam-block-size option at
server startup.
A key cache can be restructured at any time by updating its parameter values. For example:
mysql> SET GLOBAL cold_cache.key_buffer_size=4*1024*1024;
If you assign to either the
key_buffer_size or
key_cache_block_size key
cache component a value that differs from the component's
current value, the server destroys the cache's old structure
and creates a new one based on the new values. If the cache
contains any dirty blocks, the server saves them to disk
before destroying and re-creating the cache. Restructuring
does not occur if you change other key cache parameters.
When restructuring a key cache, the server first flushes the contents of any dirty buffers to disk. After that, the cache contents become unavailable. However, restructuring does not block queries that need to use indexes assigned to the cache. Instead, the server directly accesses the table indexes using native file system caching. File system caching is not as efficient as using a key cache, so although queries execute, a slowdown can be anticipated. After the cache has been restructured, it becomes available again for caching indexes assigned to it, and the use of file system caching for the indexes ceases.
The query cache stores the text of a
SELECT statement together with
the corresponding result that was sent to the client. If an
identical statement is received later, the server retrieves the
results from the query cache rather than parsing and executing
the statement again. The query cache is shared among sessions,
so a result set generated by one client can be sent in response
to the same query issued by another client.
The query cache can be useful in an environment where you have
tables that do not change very often and for which the server
receives many identical queries. This is a typical situation for
many Web servers that generate many dynamic pages based on
database content. For example, when an order form queries a
table to display the lists of all US states or all countries in
the world, those values can be retrieved from the query cache.
Although the values would probably be retrieved from memory in
any case (from the InnoDB buffer pool or
MyISAM key cache), using the query cache
avoids the overhead of processing the query, deciding whether to
use a table scan, and locating the data block for each row.
The query cache always contains current and reliable data. Any insert, update, delete, or other modification to a table causes any relevant entries in the query cache to be flushed.
The query cache does not work in an environment where you have
multiple mysqld servers updating the same
MyISAM tables.
The query cache is used for prepared statements under the conditions described in Section 8.10.3.1, “How the Query Cache Operates”.
As of MySQL 5.5.23, the query cache is not supported for partitioned tables, and is automatically disabled for queries involving partitioned tables. The query cache cannot be enabled for such queries. (Bug #53775)
Some performance data for the query cache follows. These results were generated by running the MySQL benchmark suite on a Linux Alpha 2×500MHz system with 2GB RAM and a 64MB query cache.
If all the queries you are performing are simple (such as selecting a row from a table with one row), but still differ so that the queries cannot be cached, the overhead for having the query cache active is 13%. This could be regarded as the worst case scenario. In real life, queries tend to be much more complicated, so the overhead normally is significantly lower.
Searches for a single row in a single-row table are 238% faster with the query cache than without it. This can be regarded as close to the minimum speedup to be expected for a query that is cached.
To disable the query cache at server startup, set the
query_cache_size system
variable to 0. By disabling the query cache code, there is no
noticeable overhead.
The query cache offers the potential for substantial performance improvement, but do not assume that it will do so under all circumstances. With some query cache configurations or server workloads, you might actually see a performance decrease:
Be cautious about sizing the query cache excessively large, which increases the overhead required to maintain the cache, possibly beyond the benefit of enabling it. Sizes in tens of megabytes are usually beneficial. Sizes in the hundreds of megabytes might not be.
Server workload has a significant effect on query cache
efficiency. A query mix consisting almost entirely of a
fixed set of SELECT
statements is much more likely to benefit from enabling the
cache than a mix in which frequent
INSERT statements cause
continual invalidation of results in the cache. In some
cases, a workaround is to use the
SQL_NO_CACHE option to prevent results
from even entering the cache for
SELECT statements that use
frequently modified tables. (See
Section 8.10.3.2, “Query Cache SELECT Options”.)
To verify that enabling the query cache is beneficial, test the operation of your MySQL server with the cache enabled and disabled. Then retest periodically because query cache efficiency may change as server workload changes.
This section describes how the query cache works when it is operational. Section 8.10.3.3, “Query Cache Configuration”, describes how to control whether it is operational.
Incoming queries are compared to those in the query cache before parsing, so the following two queries are regarded as different by the query cache:
SELECT * FROMtbl_nameSelect * fromtbl_name
Queries must be exactly the same (byte for byte) to be seen as identical. In addition, query strings that are identical may be treated as different for other reasons. Queries that use different databases, different protocol versions, or different default character sets are considered different queries and are cached separately.
The cache is not used for queries of the following types:
Queries that are a subquery of an outer query
Queries executed within the body of a stored function, trigger, or event
Before a query result is fetched from the query cache, MySQL
checks whether the user has
SELECT privilege for all
databases and tables involved. If this is not the case, the
cached result is not used.
If a query result is returned from query cache, the server
increments the Qcache_hits
status variable, not Com_select. See
Section 8.10.3.4, “Query Cache Status and Maintenance”.
If a table changes, all cached queries that use the table
become invalid and are removed from the cache. This includes
queries that use MERGE tables that map to
the changed table. A table can be changed by many types of
statements, such as INSERT,
UPDATE,
DELETE,
TRUNCATE TABLE,
ALTER TABLE,
DROP TABLE, or
DROP DATABASE.
The query cache also works within transactions when using
InnoDB tables.
The result from a SELECT query
on a view is cached.
The query cache works for SELECT SQL_CALC_FOUND_ROWS
... queries and stores a value that is returned by a
following SELECT FOUND_ROWS() query.
FOUND_ROWS() returns the
correct value even if the preceding query was fetched from the
cache because the number of found rows is also stored in the
cache. The SELECT FOUND_ROWS() query itself
cannot be cached.
Prepared statements that are issued using the binary protocol
using mysql_stmt_prepare() and
mysql_stmt_execute() (see
Section 23.8.8, “C API Prepared Statements”), are subject to
limitations on caching. Comparison with statements in the
query cache is based on the text of the statement after
expansion of ? parameter markers. The
statement is compared only with other cached statements that
were executed using the binary protocol. That is, for query
cache purposes, prepared statements issued using the binary
protocol are distinct from prepared statements issued using
the text protocol (see
Section 13.5, “SQL Syntax for Prepared Statements”).
A query cannot be cached if it contains any of the functions shown in the following table.
A query also is not cached under these conditions:
It refers to user-defined functions (UDFs) or stored functions.
It refers to user variables or local stored program variables.
It refers to tables in the mysql,
INFORMATION_SCHEMA, or
performance_schema database.
(MySQL 5.5.23 and later:) It refers to any partitioned tables.
It is of any of the following forms:
SELECT ... LOCK IN SHARE MODE SELECT ... FOR UPDATE SELECT ... INTO OUTFILE ... SELECT ... INTO DUMPFILE ... SELECT * FROM ... WHERE autoincrement_col IS NULL
The last form is not cached because it is used as the ODBC workaround for obtaining the last insert ID value. See the Connector/ODBC section of Chapter 23, Connectors and APIs.
Statements within transactions that use
SERIALIZABLE isolation
level also cannot be cached because they use LOCK
IN SHARE MODE locking.
It uses TEMPORARY tables.
It does not use any tables.
It generates warnings.
The user has a column-level privilege for any of the involved tables.
Two query cache-related options may be specified in
SELECT statements:
The query result is cached if it is cacheable and the
value of the
query_cache_type system
variable is ON or
DEMAND.
SQL_NO_CACHE
The server does not use the query cache. It neither checks
the query cache to see whether the result is already
cached, nor does it cache the query result. (Due to a
limitation in the parser, a space character must precede
and follow the SQL_NO_CACHE keyword; a
nonspace such as a newline causes the server to check the
query cache to see whether the result is already cached.)
Examples:
SELECT SQL_CACHE id, name FROM customer; SELECT SQL_NO_CACHE id, name FROM customer;
The have_query_cache server
system variable indicates whether the query cache is
available:
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
When using a standard MySQL binary, this value is always
YES, even if query caching is disabled.
Several other system variables control query cache operation.
These can be set in an option file or on the command line when
starting mysqld. The query cache system
variables all have names that begin with
query_cache_. They are described briefly in
Section 5.1.4, “Server System Variables”, with additional
configuration information given here.
To set the size of the query cache, set the
query_cache_size system
variable. Setting it to 0 disables the query cache. The
default size is 0, so the query cache is disabled by default.
To reduce overhead significantly, also start the server with
query_cache_type=0 if you
will not be using the query cache.
When using the Windows Configuration Wizard to install or
configure MySQL, the default value for
query_cache_size will be
configured automatically for you based on the different
configuration types available. When using the Windows
Configuration Wizard, the query cache may be enabled (that
is, set to a nonzero value) due to the selected
configuration. The query cache is also controlled by the
setting of the
query_cache_type variable.
Check the values of these variables as set in your
my.ini file after configuration has
taken place.
When you set query_cache_size
to a nonzero value, keep in mind that the query cache needs a
minimum size of about 40KB to allocate its structures. (The
exact size depends on system architecture.) If you set the
value too small, you'll get a warning, as in this example:
mysql>SET GLOBAL query_cache_size = 40000;Query OK, 0 rows affected, 1 warning (0.00 sec) mysql>SHOW WARNINGS\G*************************** 1. row *************************** Level: Warning Code: 1282 Message: Query cache failed to set size 39936; new query cache size is 0 mysql>SET GLOBAL query_cache_size = 41984;Query OK, 0 rows affected (0.00 sec) mysql>SHOW VARIABLES LIKE 'query_cache_size';+------------------+-------+ | Variable_name | Value | +------------------+-------+ | query_cache_size | 41984 | +------------------+-------+
For the query cache to actually be able to hold any query results, its size must be set larger:
mysql>SET GLOBAL query_cache_size = 1000000;Query OK, 0 rows affected (0.04 sec) mysql>SHOW VARIABLES LIKE 'query_cache_size';+------------------+--------+ | Variable_name | Value | +------------------+--------+ | query_cache_size | 999424 | +------------------+--------+ 1 row in set (0.00 sec)
The query_cache_size value is
aligned to the nearest 1024 byte block. The value reported may
therefore be different from the value that you assign.
If the query cache size is greater than 0, the
query_cache_type variable
influences how it works. This variable can be set to the
following values:
A value of 0 or OFF
prevents caching or retrieval of cached results.
A value of 1 or ON
enables caching except of those statements that begin with
SELECT SQL_NO_CACHE.
A value of 2 or
DEMAND causes caching of only those
statements that begin with SELECT
SQL_CACHE.
If query_cache_size is 0, you
should also set
query_cache_type variable to
0. In this case, the server does not acquire the query cache
mutex at all, which means that the query cache cannot be
enabled at runtime and there is reduced overhead in query
execution.
Setting the GLOBAL
query_cache_type value
determines query cache behavior for all clients that connect
after the change is made. Individual clients can control cache
behavior for their own connection by setting the
SESSION
query_cache_type value. For
example, a client can disable use of the query cache for its
own queries like this:
mysql> SET SESSION query_cache_type = OFF;
If you set query_cache_type
at server startup (rather than at runtime with a
SET
statement), only the numeric values are permitted.
To control the maximum size of individual query results that
can be cached, set the
query_cache_limit system
variable. The default value is 1MB.
Be careful not to set the size of the cache too large. Due to the need for threads to lock the cache during updates, you may see lock contention issues with a very large cache.
You can set the maximum size that can be specified for the
query cache at runtime with the
SET
statement by using the
--maximum-query_cache_size=
option on the command line or in the configuration file.
32M
When a query is to be cached, its result (the data sent to the
client) is stored in the query cache during result retrieval.
Therefore the data usually is not handled in one big chunk.
The query cache allocates blocks for storing this data on
demand, so when one block is filled, a new block is allocated.
Because memory allocation operation is costly (timewise), the
query cache allocates blocks with a minimum size given by the
query_cache_min_res_unit
system variable. When a query is executed, the last result
block is trimmed to the actual data size so that unused memory
is freed. Depending on the types of queries your server
executes, you might find it helpful to tune the value of
query_cache_min_res_unit:
The default value of
query_cache_min_res_unit
is 4KB. This should be adequate for most cases.
If you have a lot of queries with small results, the
default block size may lead to memory fragmentation, as
indicated by a large number of free blocks. Fragmentation
can force the query cache to prune (delete) queries from
the cache due to lack of memory. In this case, decrease
the value of
query_cache_min_res_unit.
The number of free blocks and queries removed due to
pruning are given by the values of the
Qcache_free_blocks and
Qcache_lowmem_prunes
status variables.
If most of your queries have large results (check the
Qcache_total_blocks and
Qcache_queries_in_cache
status variables), you can increase performance by
increasing
query_cache_min_res_unit.
However, be careful to not make it too large (see the
previous item).
To check whether the query cache is present in your MySQL server, use the following statement:
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
You can defragment the query cache to better utilize its
memory with the FLUSH
QUERY CACHE statement. The statement does not remove
any queries from the cache.
The RESET QUERY CACHE statement removes all
query results from the query cache. The
FLUSH TABLES
statement also does this.
To monitor query cache performance, use
SHOW STATUS to view the cache
status variables:
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 36 |
| Qcache_free_memory | 138488 |
| Qcache_hits | 79570 |
| Qcache_inserts | 27087 |
| Qcache_lowmem_prunes | 3114 |
| Qcache_not_cached | 22989 |
| Qcache_queries_in_cache | 415 |
| Qcache_total_blocks | 912 |
+-------------------------+--------+
Descriptions of each of these variables are given in Section 5.1.6, “Server Status Variables”. Some uses for them are described here.
The total number of SELECT
queries is given by this formula:
Com_select + Qcache_hits + queries with errors found by parser
The Com_select value is given by this
formula:
Qcache_inserts + Qcache_not_cached + queries with errors found during the column-privileges check
The query cache uses variable-length blocks, so
Qcache_total_blocks and
Qcache_free_blocks may
indicate query cache memory fragmentation. After
FLUSH QUERY
CACHE, only a single free block remains.
Every cached query requires a minimum of two blocks (one for the query text and one or more for the query results). Also, every table that is used by a query requires one block. However, if two or more queries use the same table, only one table block needs to be allocated.
The information provided by the
Qcache_lowmem_prunes status
variable can help you tune the query cache size. It counts the
number of queries that have been removed from the cache to
free up memory for caching new queries. The query cache uses a
least recently used (LRU) strategy to decide which queries to
remove from the cache. Tuning information is given in
Section 8.10.3.3, “Query Cache Configuration”.
When your database is busy with multiple sessions reading and writing data, the mechanism that controls access to data files and memory areas can become a consideration for performance tuning. Otherwise, sessions can spend time waiting for access to resources when they could be running concurrently.
MySQL manages contention for table contents using locking:
Internal locking is performed within the MySQL server itself to manage contention for table contents by multiple threads. This type of locking is internal because it is performed entirely by the server and involves no other programs. See Section 8.11.1, “Internal Locking Methods”.
External locking occurs when the server and other programs
lock MyISAM table files to
coordinate among themselves which program can access the
tables at which time. See Section 8.11.5, “External Locking”.
This section discusses internal locking; that is, locking performed within the MySQL server itself to manage contention for table contents by multiple sessions. This type of locking is internal because it is performed entirely by the server and involves no other programs. For locking performed on MySQL files by other programs, see Section 8.11.5, “External Locking”.
MySQL uses row-level
locking for InnoDB tables to support
simultaneous write access by multiple sessions, making them
suitable for multi-user, highly concurrent, and OLTP
applications.
To avoid deadlocks when
performing multiple concurrent write operations on a single
InnoDB table, acquire necessary locks at the
start of the transaction by issuing a SELECT ... FOR
UPDATE statement for each group of rows expected to be
modified, even if the data change statements come later in the
transaction. If transactions modify or lock more than one table,
issue the applicable statements in the same order within each
transaction. Deadlocks affect performance rather than
representing a serious error, because InnoDB
automatically
detects deadlock
conditions and rolls back one of the affected transactions.
Advantages of row-level locking:
Fewer lock conflicts when different sessions access different rows.
Fewer changes for rollbacks.
Possible to lock a single row for a long time.
MySQL uses table-level
locking for MyISAM,
MEMORY, and MERGE tables,
permitting only one session to update those tables at a time.
This locking level makes these storage engines more suitable for
read-only, read-mostly, or single-user applications.
These storage engines avoid deadlocks by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order. The tradeoff is that this strategy reduces concurrency; other sessions that want to modify the table must wait until the current data change statement finishes.
Advantages of table-level locking:
Relatively little memory required (row locking requires memory per row or group of rows locked)
Fast when used on a large part of the table because only a single lock is involved.
Fast if you often do GROUP BY operations
on a large part of the data or must scan the entire table
frequently.
MySQL grants table write locks as follows:
If there are no locks on the table, put a write lock on it.
Otherwise, put the lock request in the write lock queue.
MySQL grants table read locks as follows:
If there are no write locks on the table, put a read lock on it.
Otherwise, put the lock request in the read lock queue.
Table updates are given higher priority than table retrievals.
Therefore, when a lock is released, the lock is made available
to the requests in the write lock queue and then to the requests
in the read lock queue. This ensures that updates to a table are
not “starved” even when there is heavy
SELECT activity for the table.
However, if there are many updates for a table,
SELECT statements wait until
there are no more updates.
For information on altering the priority of reads and writes, see Section 8.11.2, “Table Locking Issues”.
You can analyze the table lock contention on your system by
checking the
Table_locks_immediate and
Table_locks_waited status
variables, which indicate the number of times that requests for
table locks could be granted immediately and the number that had
to wait, respectively:
mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+-----------------------+---------+
The MyISAM storage engine supports concurrent
inserts to reduce contention between readers and writers for a
given table: If a MyISAM table has no free
blocks in the middle of the data file, rows are always inserted
at the end of the data file. In this case, you can freely mix
concurrent INSERT and
SELECT statements for a
MyISAM table without locks. That is, you can
insert rows into a MyISAM table at the same
time other clients are reading from it. Holes can result from
rows having been deleted from or updated in the middle of the
table. If there are holes, concurrent inserts are disabled but
are enabled again automatically when all holes have been filled
with new data. To control this behavior, use the
concurrent_insert system
variable. See Section 8.11.3, “Concurrent Inserts”.
If you acquire a table lock explicitly with
LOCK TABLES, you can request a
READ LOCAL lock rather than a
READ lock to enable other sessions to perform
concurrent inserts while you have the table locked.
To perform many INSERT and
SELECT operations on a table
t1 when concurrent inserts are not possible,
you can insert rows into a temporary table
temp_t1 and update the real table with the
rows from the temporary table:
mysql>LOCK TABLES t1 WRITE, temp_t1 WRITE;mysql>INSERT INTO t1 SELECT * FROM temp_t1;mysql>DELETE FROM temp_t1;mysql>UNLOCK TABLES;
Generally, table locks are superior to row-level locks in the following cases:
Most statements for the table are reads.
Statements for the table are a mix of reads and writes, where writes are updates or deletes for a single row that can be fetched with one key read:
UPDATEtbl_nameSETcolumn=valueWHEREunique_key_col=key_value; DELETE FROMtbl_nameWHEREunique_key_col=key_value;
SELECT combined with
concurrent INSERT statements,
and very few UPDATE or
DELETE statements.
Many scans or GROUP BY operations on the
entire table without any writers.
With higher-level locks, you can more easily tune applications by supporting locks of different types, because the lock overhead is less than for row-level locks.
Options other than row-level locking:
Versioning (such as that used in MySQL for concurrent inserts) where it is possible to have one writer at the same time as many readers. This means that the database or table supports different views for the data depending on when access begins. Other common terms for this are “time travel,” “copy on write,” or “copy on demand.”
Copy on demand is in many cases superior to row-level locking. However, in the worst case, it can use much more memory than using normal locks.
Instead of using row-level locks, you can employ
application-level locks, such as those provided by
GET_LOCK() and
RELEASE_LOCK() in MySQL.
These are advisory locks, so they work only with
applications that cooperate with each other. See
Section 12.16, “Miscellaneous Functions”.
InnoDB tables use row-level locking so that
multiple sessions and applications can read from and write to
the same table simultaneously, without making each other wait or
producing inconsistent results. For this storage engine, avoid
using the LOCK TABLES statement,
because it does not offer any extra protection, but instead
reduces concurrency. The automatic row-level locking makes these
tables suitable for your busiest databases with your most
important data, while also simplifying application logic since
you do not need to lock and unlock tables. Consequently, the
InnoDB storage engine is the default in MySQL
5.5 and higher.
MySQL uses table locking (instead of page, row, or column
locking) for all storage engines except
InnoDB and NDB.
The locking operations themselves do not have much overhead. But
because only one session can write to a table at any one time,
for best performance with these other storage engines, use them
primarily for tables that are queried often and rarely inserted
into or updated.
When choosing whether to create a table using
InnoDB or a different storage engine, keep in
mind the following disadvantages of table locking:
Table locking enables many sessions to read from a table at the same time, but if a session wants to write to a table, it must first get exclusive access, meaning it might have to wait for other sessions to finish with the table first. During the update, all other sessions that want to access this particular table must wait until the update is done.
Table locking causes problems when a session is waiting because the disk is full and free space needs to become available before the session can proceed. In this case, all sessions that want to access the problem table are also put in a waiting state until more disk space is made available.
A SELECT statement that takes
a long time to run prevents other sessions from updating the
table in the meantime, making the other sessions appear slow
or unresponsive. While a session is waiting to get exclusive
access to the table for updates, other sessions that issue
SELECT statements will queue
up behind it, reducing concurrency even for read-only
sessions.
The following items describe some ways to avoid or reduce contention caused by table locking:
Consider switching the table to the
InnoDB storage engine, either using
CREATE TABLE ... ENGINE=INNODB during
setup, or using ALTER TABLE ...
ENGINE=INNODB for an existing table. See
Chapter 14, The InnoDB Storage Engine for more details
about this storage engine.
Optimize SELECT statements to
run faster so that they lock tables for a shorter time. You
might have to create some summary tables to do this.
Start mysqld with
--low-priority-updates. For
storage engines that use only table-level locking (such as
MyISAM, MEMORY, and
MERGE), this gives all statements that
update (modify) a table lower priority than
SELECT statements. In this
case, the second SELECT
statement in the preceding scenario would execute before the
UPDATE statement, and would
not wait for the first SELECT
to finish.
To specify that all updates issued in a specific connection
should be done with low priority, set the
low_priority_updates server
system variable equal to 1.
To give a specific INSERT,
UPDATE, or
DELETE statement lower
priority, use the LOW_PRIORITY attribute.
To give a specific SELECT
statement higher priority, use the
HIGH_PRIORITY attribute. See
Section 13.2.9, “SELECT Syntax”.
Start mysqld with a low value for the
max_write_lock_count system
variable to force MySQL to temporarily elevate the priority
of all SELECT statements that
are waiting for a table after a specific number of inserts
to the table occur. This permits READ
locks after a certain number of WRITE
locks.
If you have problems with
INSERT combined with
SELECT, consider switching to
MyISAM tables, which support concurrent
SELECT and
INSERT statements. (See
Section 8.11.3, “Concurrent Inserts”.)
If you mix inserts and deletes on the same table,
INSERT DELAYED may be of
great help. See Section 13.2.5.2, “INSERT DELAYED Syntax”.
If you have problems with mixed
SELECT and
DELETE statements, the
LIMIT option to
DELETE may help. See
Section 13.2.2, “DELETE Syntax”.
Using SQL_BUFFER_RESULT with
SELECT statements can help to
make the duration of table locks shorter. See
Section 13.2.9, “SELECT Syntax”.
Splitting table contents into separate tables may help, by allowing queries to run against columns in one table, while updates are confined to columns in a different table.
You could change the locking code in
mysys/thr_lock.c to use a single queue.
In this case, write locks and read locks would have the same
priority, which might help some applications.
The MyISAM storage engine supports concurrent
inserts to reduce contention between readers and writers for a
given table: If a MyISAM table has no holes
in the data file (deleted rows in the middle), an
INSERT statement can be executed
to add rows to the end of the table at the same time that
SELECT statements are reading
rows from the table. If there are multiple
INSERT statements, they are
queued and performed in sequence, concurrently with the
SELECT statements. The results of
a concurrent INSERT may not be
visible immediately.
The concurrent_insert system
variable can be set to modify the concurrent-insert processing.
By default, the variable is set to AUTO (or
1) and concurrent inserts are handled as just described. If
concurrent_insert is set to
NEVER (or 0), concurrent inserts are
disabled. If the variable is set to ALWAYS
(or 2), concurrent inserts at the end of the table are permitted
even for tables that have deleted rows. See also the description
of the concurrent_insert system
variable.
Under circumstances where concurrent inserts can be used, there
is seldom any need to use the DELAYED
modifier for INSERT statements.
See Section 13.2.5.2, “INSERT DELAYED Syntax”.
If you are using the binary log, concurrent inserts are
converted to normal inserts for CREATE ...
SELECT or
INSERT ...
SELECT statements. This is done to ensure that you can
re-create an exact copy of your tables by applying the log
during a backup operation. See Section 5.4.4, “The Binary Log”. In
addition, for those statements a read lock is placed on the
selected-from table such that inserts into that table are
blocked. The effect is that concurrent inserts for that table
must wait as well.
With LOAD DATA
INFILE, if you specify CONCURRENT
with a MyISAM table that satisfies the
condition for concurrent inserts (that is, it contains no free
blocks in the middle), other sessions can retrieve data from the
table while LOAD DATA is
executing. Use of the CONCURRENT option
affects the performance of LOAD
DATA a bit, even if no other session is using the
table at the same time.
If you specify HIGH_PRIORITY, it overrides
the effect of the
--low-priority-updates option if
the server was started with that option. It also causes
concurrent inserts not to be used.
For LOCK
TABLE, the difference between READ
LOCAL and READ is that
READ LOCAL permits nonconflicting
INSERT statements (concurrent
inserts) to execute while the lock is held. However, this cannot
be used if you are going to manipulate the database using
processes external to the server while you hold the lock.
MySQL 5.5.3 and up uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking applies not just to tables, but also to schemas and stored programs (procedures, functions, triggers, and scheduled events).
Metadata locking does involve some overhead, which increases as query volume increases. Metadata contention increases the more that multiple queries attempt to access the same objects.
Metadata locking is not a replacement for the table definition
cache, and its mutexes and locks differ from the
LOCK_open mutex. The following discussion
provides some information about how metadata locking works.
To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table's structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.
This principle applies not only to transactional tables, but
also to nontransactional tables. Suppose that a session begins a
transaction that uses transactional table t
and nontransactional table nt as follows:
START TRANSACTION; SELECT * FROM t; SELECT * FROM nt;
The server holds metadata locks on both t and
nt until the transaction ends. If another
session attempts a DDL or write lock operation on either table,
it blocks until metadata lock release at transaction end. For
example, a second session blocks if it attempts any of these
operations:
DROP TABLE t; ALTER TABLE t ...; DROP TABLE nt; ALTER TABLE nt ...; LOCK TABLE t ... WRITE;
If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.
In autocommit mode, each statement is in effect a complete transaction, so metadata locks acquired for the statement are held only to the end of the statement.
Metadata locks acquired during a
PREPARE statement are released
once the statement has been prepared, even if preparation occurs
within a multiple-statement transaction.
Before MySQL 5.5.3, when a transaction acquired the equivalent of a metadata lock for a table used within a statement, it released the lock at the end of the statement. This approach had the disadvantage that if a DDL statement occurred for a table that was being used by another session in an active transaction, statements could be written to the binary log in the wrong order.
External locking is the use of file system locking to manage
contention for MyISAM database
tables by multiple processes. External locking is used in
situations where a single process such as the MySQL server
cannot be assumed to be the only process that requires access to
tables. Here are some examples:
If you run multiple servers that use the same database directory (not recommended), each server must have external locking enabled.
If you use myisamchk to perform table
maintenance operations on
MyISAM tables, you must either
ensure that the server is not running, or that the server
has external locking enabled so that it locks table files as
necessary to coordinate with myisamchk
for access to the tables. The same is true for use of
myisampack to pack
MyISAM tables.
If the server is run with external locking enabled, you can use myisamchk at any time for read operations such a checking tables. In this case, if the server tries to update a table that myisamchk is using, the server will wait for myisamchk to finish before it continues.
If you use myisamchk for write operations such as repairing or optimizing tables, or if you use myisampack to pack tables, you must always ensure that the mysqld server is not using the table. If you do not stop mysqld, at least do a mysqladmin flush-tables before you run myisamchk. Your tables may become corrupted if the server and myisamchk access the tables simultaneously.
With external locking in effect, each process that requires access to a table acquires a file system lock for the table files before proceeding to access the table. If all necessary locks cannot be acquired, the process is blocked from accessing the table until the locks can be obtained (after the process that currently holds the locks releases them).
External locking affects server performance because the server must sometimes wait for other processes before it can access tables.
External locking is unnecessary if you run a single server to access a given data directory (which is the usual case) and if no other programs such as myisamchk need to modify tables while the server is running. If you only read tables with other programs, external locking is not required, although myisamchk might report warnings if the server changes tables while myisamchk is reading them.
With external locking disabled, to use
myisamchk, you must either stop the server
while myisamchk executes or else lock and
flush the tables before running myisamchk.
(See Section 8.12.1, “System Factors and Startup Parameter Tuning”.) To avoid this
requirement, use the CHECK TABLE
and REPAIR TABLE statements to
check and repair MyISAM tables.
For mysqld, external locking is controlled by
the value of the
skip_external_locking system
variable. When this variable is enabled, external locking is
disabled, and vice versa. External locking is disabled by
default.
Use of external locking can be controlled at server startup by
using the --external-locking or
--skip-external-locking
option.
If you do use external locking option to enable updates to
MyISAM tables from many MySQL
processes, you must ensure that the following conditions are
satisfied:
Do not use the query cache for queries that use tables that are updated by another process.
Do not start the server with the
--delay-key-write=ALL option
or use the DELAY_KEY_WRITE=1 table option
for any shared tables. Otherwise, index corruption can
occur.
The easiest way to satisfy these conditions is to always use
--external-locking together with
--delay-key-write=OFF and
--query-cache-size=0. (This is
not done by default because in many setups it is useful to have
a mixture of the preceding options.)
This section discusses optimization techniques for the database server, primarily dealing with system configuration rather than tuning SQL statements. The information in this section is appropriate for DBAs who want to ensure performance and scalability across the servers they manage; for developers constructing installation scripts that include setting up the database; and people running MySQL themselves for development, testing, and so on who want to maximize their own productivity.
We start with system-level factors, because some of these decisions must be made very early to achieve large performance gains. In other cases, a quick look at this section may suffice. However, it is always nice to have a sense of how much can be gained by changing factors that apply at this level.
Before using MySQL in production, test it on your intended platform.
Other tips:
If you have enough RAM, you could remove all swap devices. Some operating systems use a swap device in some contexts even if you have free memory.
Avoid external locking for
MyISAM tables. The default is
for external locking to be disabled. The
--external-locking and
--skip-external-locking
options explicitly enable and disable external locking.
Disabling external locking does not affect MySQL's functionality as long as you run only one server. Just remember to take down the server (or lock and flush the relevant tables) before you run myisamchk. On some systems it is mandatory to disable external locking because it does not work, anyway.
The only case in which you cannot disable external locking is when you run multiple MySQL servers (not clients) on the same data, or if you run myisamchk to check (not repair) a table without telling the server to flush and lock the tables first. Note that using multiple MySQL servers to access the same data concurrently is generally not recommended, except when using MySQL Cluster.
The LOCK TABLES and
UNLOCK
TABLES statements use internal locking, so you can
use them even if external locking is disabled.
You can determine the default buffer sizes used by the mysqld server using this command:
shell> mysqld --verbose --help
This command produces a list of all mysqld options and configurable system variables. The output includes the default variable values and looks something like this:
abort-slave-event-count 0 allow-suspicious-udfs FALSE archive ON auto-increment-increment 1 auto-increment-offset 1 autocommit TRUE automatic-sp-privileges TRUE back-log 50 basedir /home/jon/bin/mysql-5.5-cluster/ ... tmpdir /tmp transaction-alloc-block-size 8192 transaction-isolation REPEATABLE-READ transaction-prealloc-size 4096 updatable-views-with-limit YES verbose TRUE wait-timeout 28800
For a mysqld server that is currently running, you can see the current values of its system variables by connecting to it and issuing this statement:
mysql> SHOW VARIABLES;
You can also see some statistical and status indicators for a running server by issuing this statement:
mysql> SHOW STATUS;
System variable and status information also can be obtained using mysqladmin:
shell>mysqladmin variablesshell>mysqladmin extended-status
For a full description of all system and status variables, see Section 5.1.4, “Server System Variables”, and Section 5.1.6, “Server Status Variables”.
MySQL uses algorithms that are very scalable, so you can usually run with very little memory. However, normally better performance results from giving MySQL more memory.
When tuning a MySQL server, the two most important variables to
configure are key_buffer_size
and table_open_cache. You
should first feel confident that you have these set
appropriately before trying to change any other variables.
The following examples indicate some typical variable values for different runtime configurations.
If you have at least 1-2GB of memory and many tables and want maximum performance with a moderate number of clients, use something like this:
shell>mysqld_safe --key_buffer_size=384M --table_open_cache=4000 \--sort_buffer_size=4M --read_buffer_size=1M &
If you have only 256MB of memory and only a few tables, but you still do a lot of sorting, you can use something like this:
shell> mysqld_safe --key_buffer_size=64M --sort_buffer_size=1M
If there are very many simultaneous connections, swapping problems may occur unless mysqld has been configured to use very little memory for each connection. mysqld performs better if you have enough memory for all connections.
With little memory and lots of connections, use something like this:
shell>mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \--read_buffer_size=100K &
Or even this:
shell>mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \--table_open_cache=32 --read_buffer_size=8K \--net_buffer_length=1K &
If you are performing GROUP BY or
ORDER BY operations on tables that are much
larger than your available memory, increase the value of
read_rnd_buffer_size to speed
up the reading of rows following sorting operations.
You can make use of the example option files included with your MySQL distribution; see Section 5.1.2, “Server Configuration Defaults”.
If you specify an option on the command line for mysqld or mysqld_safe, it remains in effect only for that invocation of the server. To use the option every time the server runs, put it in an option file.
To see the effects of a parameter change, do something like this:
shell> mysqld --key_buffer_size=128M --verbose --help
The variable values are listed near the end of the output. Make
sure that the --verbose and
--help options are last.
Otherwise, the effect of any options listed after them on the
command line are not reflected in the output.
For information on tuning the InnoDB storage
engine, see Section 8.5, “Optimizing for InnoDB Tables”.
This section describes ways to configure storage devices when
you can devote more and faster storage hardware to the database
server. For information about optimizing an
InnoDB configuration to improve I/O
performance, see Section 8.5.7, “Optimizing InnoDB Disk I/O”.
Disk seeks are a huge performance bottleneck. This problem becomes more apparent when the amount of data starts to grow so large that effective caching becomes impossible. For large databases where you access data more or less randomly, you can be sure that you need at least one disk seek to read and a couple of disk seeks to write things. To minimize this problem, use disks with low seek times.
Increase the number of available disk spindles (and thereby reduce the seek overhead) by either symlinking files to different disks or striping the disks:
Using symbolic links
This means that, for MyISAM tables,
you symlink the index file and data files from their
usual location in the data directory to another disk
(that may also be striped). This makes both the seek and
read times better, assuming that the disk is not used
for other purposes as well. See
Section 8.12.4, “Using Symbolic Links”.
Symbolic links are not supported for use with
InnoDB tables.
Striping means that you have many disks and put the
first block on the first disk, the second block on the
second disk, and the N-th
block on the (N MOD
number_of_disks
The speed difference for striping is very dependent on the parameters. Depending on how you set the striping parameters and number of disks, you may get differences measured in orders of magnitude. You have to choose to optimize for random or sequential access.
For reliability, you may want to use RAID 0+1 (striping plus
mirroring), but in this case, you need 2 ×
N drives to hold
N drives of data. This is
probably the best option if you have the money for it.
However, you may also have to invest in some
volume-management software to handle it efficiently.
A good option is to vary the RAID level according to how
critical a type of data is. For example, store
semi-important data that can be regenerated on a RAID 0
disk, but store really important data such as host
information and logs on a RAID 0+1 or RAID
N disk. RAID
N can be a problem if you have
many writes, due to the time required to update the parity
bits.
On Linux, you can get much better performance by using
hdparm to configure your disk's
interface. (Up to 100% under load is not uncommon.) The
following hdparm options should be quite
good for MySQL, and probably for many other applications:
hdparm -m 16 -d 1
Performance and reliability when using this command depend
on your hardware, so we strongly suggest that you test your
system thoroughly after using hdparm.
Please consult the hdparm manual page for
more information. If hdparm is not used
wisely, file system corruption may result, so back up
everything before experimenting!
You can also set the parameters for the file system that the database uses:
If you do not need to know when files were last accessed
(which is not really useful on a database server), you can
mount your file systems with the -o noatime
option. That skips updates to the last access time in inodes
on the file system, which avoids some disk seeks.
On many operating systems, you can set a file system to be
updated asynchronously by mounting it with the -o
async option. If your computer is reasonably
stable, this should give you better performance without
sacrificing too much reliability. (This flag is on by
default on Linux.)
You can move databases or tables from the database directory to other locations and replace them with symbolic links to the new locations. You might want to do this, for example, to move a database to a file system with more free space or increase the speed of your system by spreading your tables to different disks.
The recommended way to do this is to symlink entire database
directories to a different disk. Symlink
MyISAM tables only as a last resort.
To determine the location of your data directory, use this statement:
SHOW VARIABLES LIKE 'datadir';
On Unix, the way to symlink a database is first to create a directory on some disk where you have free space and then to create a soft link to it from the MySQL data directory.
shell>mkdir /dr1/databases/testshell>ln -s /dr1/databases/test/path/to/datadir
MySQL does not support linking one directory to multiple
databases. Replacing a database directory with a symbolic link
works as long as you do not make a symbolic link between
databases. Suppose that you have a database
db1 under the MySQL data directory, and
then make a symlink db2 that points to
db1:
shell>cdshell>/path/to/datadirln -s db1 db2
The result is that, or any table tbl_a in
db1, there also appears to be a table
tbl_a in db2. If one
client updates db1.tbl_a and another client
updates db2.tbl_a, problems are likely to
occur.
Symlinks are fully supported only for
MyISAM tables. For files used by tables for
other storage engines, you may get strange problems if you try
to use symbolic links.
Do not symlink tables on systems that do not have a fully
operational realpath() call. (Linux and
Solaris support realpath()). To determine
whether your system supports symbolic links, check the value
of the have_symlink system
variable using this statement:
SHOW VARIABLES LIKE 'have_symlink';
The handling of symbolic links for MyISAM
tables works as follows:
In the data directory, you always have the table format
(.frm) file, the data
(.MYD) file, and the index
(.MYI) file. The data file and index
file can be moved elsewhere and replaced in the data
directory by symlinks. The format file cannot.
You can symlink the data file and the index file independently to different directories.
To instruct a running MySQL server to perform the
symlinking, use the DATA DIRECTORY and
INDEX DIRECTORY options to
CREATE TABLE. See
Section 13.1.17, “CREATE TABLE Syntax”. Alternatively, if
mysqld is not running, symlinking can
be accomplished manually using ln -s
from the command line.
The path used with either or both of the DATA
DIRECTORY and INDEX
DIRECTORY options may not include the MySQL
data directory. (Bug #32167)
myisamchk does not replace a symlink
with the data file or index file. It works directly on the
file to which the symlink points. Any temporary files are
created in the directory where the data file or index file
is located. The same is true for the
ALTER TABLE,
OPTIMIZE TABLE, and
REPAIR TABLE statements.
When you drop a table that is using symlinks,
both the symlink and the file to which the
symlink points are dropped. This is an
extremely good reason not to run
mysqld as the system
root or permit system users to have
write access to MySQL database directories.
If you rename a table with
ALTER TABLE
... RENAME or RENAME
TABLE and you do not move the table to another
database, the symlinks in the database directory are
renamed to the new names and the data file and index file
are renamed accordingly.
If you use
ALTER TABLE
... RENAME or RENAME
TABLE to move a table to another database, the
table is moved to the other database directory. If the
table name changed, the symlinks in the new database
directory are renamed to the new names and the data file
and index file are renamed accordingly.
If you are not using symlinks, start
mysqld with the
--skip-symbolic-links
option to ensure that no one can use
mysqld to drop or rename a file outside
of the data directory.
These table symlink operations are not supported:
ALTER TABLE ignores the
DATA DIRECTORY and INDEX
DIRECTORY table options.
As indicated previously, only the data and index files can
be symbolic links. The .frm file must
never be a symbolic link. Attempting
to do this (for example, to make one table name a synonym
for another) produces incorrect results. Suppose that you
have a database db1 under the MySQL
data directory, a table tbl1 in this
database, and in the db1 directory you
make a symlink tbl2 that points to
tbl1:
shell>cdshell>/path/to/datadir/db1ln -s tbl1.frm tbl2.frmshell>ln -s tbl1.MYD tbl2.MYDshell>ln -s tbl1.MYI tbl2.MYI
Problems result if one thread reads
db1.tbl1 and another thread updates
db1.tbl2:
The query cache is “fooled” (it has no
way of knowing that tbl1 has not
been updated, so it returns outdated results).
ALTER statements on
tbl2 fail.
On Windows, symbolic links can be used for database directories. This enables you to put a database directory at a different location (for example, on a different disk) by setting up a symbolic link to it. Use of database symlinks on Windows is similar to their use on Unix, although the procedure for setting up the link differs.
Suppose that you want to place the database directory for a
database named mydb at
D:\data\mydb. To do this, create a
symbolic link in the MySQL data directory that points to
D:\data\mydb. However, before creating
the symbolic link, make sure that the
D:\data\mydb directory exists by creating
it if necessary. If you already have a database directory
named mydb in the data directory, move it
to D:\data. Otherwise, the symbolic link
will be ineffective. To avoid problems, make sure that the
server is not running when you move the database directory.
The procedure for creating the database symbolic link depends on your version of Windows.
Windows Vista, Windows Server 2008, or newer have native symbolic link support, so you can create a symlink using the mklink command. This command requires administrative privileges.
Change location into the data directory:
C:\> cd \path\to\datadir
In the data directory, create a symlink named
mydb that points to the location of
the database directory:
C:\> mklink /d mydb D:\data\mydb
After this, all tables created in the database
mydb are created in
D:\data\mydb.
Alternatively, on any version of Windows supported by MySQL,
you can create a symbolic link to a MySQL database by creating
a .sym file in the data directory that
contains the path to the destination directory. The file
should be named
db_name.symdb_name is the database name.
Support for database symbolic links on Windows using
.sym files is enabled by default. If you
do not need .sym file symbolic links, you
can disable support for them by starting
mysqld with the
--skip-symbolic-links
option. To determine whether your system supports
.sym file symbolic links, check the value
of the have_symlink system
variable using this statement:
SHOW VARIABLES LIKE 'have_symlink';
To create a .sym file symlink, use this
procedure:
Change location into the data directory:
C:\> cd \path\to\datadir
In the data directory, create a text file named
mydb.sym that contains this path
name: D:\data\mydb\
The path name to the new database and tables should be
absolute. If you specify a relative path, the location
will be relative to the mydb.sym
file.
After this, all tables created in the database
mydb are created in
D:\data\mydb.
The following limitations apply to the use of
.sym files for database symbolic linking
on Windows. These limitations do not apply for symlinks
created using mklink.
The symbolic link is not used if a directory with the same name as the database exists in the MySQL data directory.
The --innodb_file_per_table
option cannot be used.
If you run mysqld as a service, you
cannot use a mapped drive to a remote server as the
destination of the symbolic link. As a workaround, you can
use the full path
(\\servername\path\).
MySQL allocates buffers and caches to improve performance of database operations. You can improve MySQL performance by increasing the values of certain cache and buffer-related system variables. You can also modify these variables to run MySQL on systems with limited memory.
The following list describes some of the ways that MySQL uses memory. Where applicable, relevant system variables are referenced. Some items are storage engine specific.
The InnoDB buffer pool is a memory area
that holds cached InnoDB data for
tables, indexes, and other auxiliary buffers. For
efficiency of high-volume read operations, the buffer pool
is divided into pages
that can potentially hold multiple rows. For efficiency of
cache management, the buffer pool is implemented as a
linked list of pages; data that is rarely used is aged out
of the cache, using a variation of the
LRU algorithm. For more
information, see Section 14.9.2.1, “The InnoDB Buffer Pool”.
The size of the buffer pool is important for system performance.
Typically, it is recommended that
innodb_buffer_pool_size
is configured to 50 to 75 percent of system memory.
InnoDB allocates memory for the
entire buffer pool at server startup. Memory
allocation is performed by malloc()
operations. Buffer pool size is defined by the
innodb_buffer_pool_size
configuration option.
On systems with a large amount of memory, you can
improve concurrency by dividing the buffer pool into
multiple
buffer pool
instances. The number of buffer pool instances
is defined by
innodb_buffer_pool_instances.
A buffer pool that is too small may cause excessive churning as pages are flushed from the buffer pool only to be required again a short time later.
A buffer pool that is too large may cause swapping due to competition for memory.
The MySQL Performance Schema is a feature for monitoring MySQL server execution at a low level. For performance reasons, fixed memory buffers for Performance Schema are allocated at server startup and do not change in size while the server is running.
All threads share the MyISAM
key buffer; its size is determined by the
key_buffer_size variable.
Other buffers used by the server are allocated as needed.
See Section 8.12.2, “Tuning Server Parameters”.
For each MyISAM table that is opened,
the index file is opened once; the data file is opened
once for each concurrently running thread. For each
concurrent thread, a table structure, column structures
for each column, and a buffer of size 3 *
are allocated
(where NN is the maximum row
length, not counting BLOB
columns). A BLOB column
requires five to eight bytes plus the length of the
BLOB data. The
MyISAM storage engine maintains one
extra row buffer for internal use.
Each thread that is used to manage client connections uses some thread-specific space. The following list indicates these and which variables control their size:
A stack (variable
thread_stack)
A connection buffer (variable
net_buffer_length)
A result buffer (variable
net_buffer_length)
The connection buffer and result buffer each begin with a
size equal to
net_buffer_length bytes,
but are dynamically enlarged up to
max_allowed_packet bytes
as needed. The result buffer shrinks to
net_buffer_length bytes
after each SQL statement. While a statement is running, a
copy of the current statement string is also allocated.
All threads share the same base memory.
When a thread is no longer needed, the memory allocated to it is released and returned to the system unless the thread goes back into the thread cache. In that case, the memory remains allocated.
The myisam_use_mmap
system variable can be set to 1 to enable memory-mapping
for all MyISAM tables.
Each request that performs a sequential scan of a table
allocates a read
buffer (variable
read_buffer_size).
When reading rows in an arbitrary sequence (for example,
following a sort), a
random-read buffer
(variable
read_rnd_buffer_size) may
be allocated to avoid disk seeks.
All joins are executed in a single pass, and most joins
can be done without even using a temporary table. Most
temporary tables are memory-based hash tables. Temporary
tables with a large row length (calculated as the sum of
all column lengths) or that contain
BLOB columns are stored on
disk.
If an internal in-memory temporary table becomes too
large, MySQL handles this automatically by changing the
table from in-memory to on-disk format, to be handled by
the MyISAM storage engine. You can
increase the permissible temporary table size as described
in Section 8.4.4, “Internal Temporary Table Use in MySQL”.
For MEMORY tables explicitly
created with CREATE TABLE,
only the
max_heap_table_size
system variable determines how large the table is
permitted to grow and there is no conversion to on-disk
format.
Most requests that perform a sort allocate a sort buffer and zero to two temporary files depending on the result set size. See Section B.5.3.5, “Where MySQL Stores Temporary Files”.
Almost all parsing and calculating is done in thread-local and reusable memory pools. No memory overhead is needed for small items, so the normal slow memory allocation and freeing is avoided. Memory is allocated only for unexpectedly large strings.
For each table having BLOB
columns, a buffer is enlarged dynamically to read in
larger BLOB values. If you
scan a table, a buffer as large as the largest
BLOB value is allocated.
MySQL requires memory and descriptors for the table cache.
Handler structures for all in-use tables are saved in the
table cache and managed as “First In, First
Out” (FIFO). The initial table cache size is
defined by the
table_open_cache system
variable; see Section 8.4.3.1, “How MySQL Opens and Closes Tables”.
MySQL also requires memory for the table definition cache.
The
table_definition_cache
system variable defines the number of table definitions
(from .frm files) that can be stored
in the table definition cache. If you use a large number
of tables, you can create a large table definition cache
to speed up the opening of tables. The table definition
cache takes less space and does not use file descriptors,
unlike the table cache.
A FLUSH
TABLES statement or mysqladmin
flush-tables command closes all tables that are
not in use at once and marks all in-use tables to be
closed when the currently executing thread finishes. This
effectively frees most in-use memory.
FLUSH
TABLES does not return until all tables have
been closed.
The server caches information in memory as a result of
GRANT,
CREATE USER,
CREATE SERVER, and
INSTALL PLUGIN statements.
This memory is not released by the corresponding
REVOKE,
DROP USER,
DROP SERVER, and
UNINSTALL PLUGIN
statements, so for a server that executes many instances
of the statements that cause caching, there will be an
increase in memory use. This cached memory can be freed
with FLUSH
PRIVILEGES.
ps and other system status programs may
report that mysqld uses a lot of memory.
This may be caused by thread stacks on different memory
addresses. For example, the Solaris version of
ps counts the unused memory between stacks
as used memory. To verify this, check available swap with
swap -s. We test mysqld
with several memory-leakage detectors (both commercial and
Open Source), so there should be no memory leaks.
Some hardware/operating system architectures support memory pages greater than the default (usually 4KB). The actual implementation of this support depends on the underlying hardware and operating system. Applications that perform a lot of memory accesses may obtain performance improvements by using large pages due to reduced Translation Lookaside Buffer (TLB) misses.
In MySQL, large pages can be used by InnoDB, to allocate memory for its buffer pool and additional memory pool.
Standard use of large pages in MySQL attempts to use the
largest size supported, up to 4MB. Under Solaris, a
“super large pages” feature enables uses of pages
up to 256MB. This feature is available for recent SPARC
platforms. It can be enabled or disabled by using the
--super-large-pages or
--skip-super-large-pages
option.
MySQL also supports the Linux implementation of large page support (which is called HugeTLB in Linux).
Before large pages can be used on Linux, the kernel must be
enabled to support them and it is necessary to configure the
HugeTLB memory pool. For reference, the HugeTBL API is
documented in the
Documentation/vm/hugetlbpage.txt file of
your Linux sources.
The kernel for some recent systems such as Red Hat Enterprise Linux appear to have the large pages feature enabled by default. To check whether this is true for your kernel, use the following command and look for output lines containing “huge”:
shell> cat /proc/meminfo | grep -i huge
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 4096 kB
The nonempty command output indicates that large page support is present, but the zero values indicate that no pages are configured for use.
If your kernel needs to be reconfigured to support large
pages, consult the hugetlbpage.txt file
for instructions.
Assuming that your Linux kernel has large page support
enabled, configure it for use by MySQL using the following
commands. Normally, you put these in an
rc file or equivalent startup file that
is executed during the system boot sequence, so that the
commands execute each time the system starts. The commands
should execute early in the boot sequence, before the MySQL
server starts. Be sure to change the allocation numbers and
the group number as appropriate for your system.
# Set the number of pages to be used. # Each page is normally 2MB, so a value of 20 = 40MB. # This command actually allocates memory, so this much # memory must be available. echo 20 > /proc/sys/vm/nr_hugepages # Set the group number that is permitted to access this # memory (102 in this case). The mysql user must be a # member of this group. echo 102 > /proc/sys/vm/hugetlb_shm_group # Increase the amount of shmem permitted per segment # (12G in this case). echo 1560281088 > /proc/sys/kernel/shmmax # Increase total amount of shared memory. The value # is the number of pages. At 4KB/page, 4194304 = 16GB. echo 4194304 > /proc/sys/kernel/shmall
For MySQL usage, you normally want the value of
shmmax to be close to the value of
shmall.
To verify the large page configuration, check
/proc/meminfo again as described
previously. Now you should see some nonzero values:
shell> cat /proc/meminfo | grep -i huge
HugePages_Total: 20
HugePages_Free: 20
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 4096 kB
The final step to make use of the
hugetlb_shm_group is to give the
mysql user an “unlimited”
value for the memlock limit. This can by done either by
editing /etc/security/limits.conf or by
adding the following command to your
mysqld_safe script:
ulimit -l unlimited
Adding the ulimit command to
mysqld_safe causes the
root user to set the memlock limit to
unlimited before switching to the
mysql user. (This assumes that
mysqld_safe is started by
root.)
Large page support in MySQL is disabled by default. To enable
it, start the server with the
--large-pages option. For
example, you can use the following lines in your server's
my.cnf file:
[mysqld] large-pages
With this option, InnoDB uses large pages
automatically for its buffer pool and additional memory pool.
If InnoDB cannot do this, it falls back to
use of traditional memory and writes a warning to the error
log: Warning: Using conventional memory
pool
To verify that large pages are being used, check
/proc/meminfo again:
shell> cat /proc/meminfo | grep -i huge
HugePages_Total: 20
HugePages_Free: 20
HugePages_Rsvd: 2
HugePages_Surp: 0
Hugepagesize: 4096 kB
Connection manager threads handle client connection requests on the network interfaces that the server listens to. On all platforms, one manager thread handles TCP/IP connection requests. On Unix, this manager thread also handles Unix socket file connection requests. On Windows, a manager thread handles shared-memory connection requests, and another handles named-pipe connection requests. The server does not create threads to handle interfaces that it does not listen to. For example, a Windows server that does not have support for named-pipe connections enabled does not create a thread to handle them.
By default, connection manager threads associate each client connection with a thread dedicated to it that handles authentication and request processing for that connection. Manager threads create a new thread when necessary but try to avoid doing so by consulting the thread cache first to see whether it contains a thread that can be used for the connection. When a connection ends, its thread is returned to the thread cache if the cache is not full.
In the default connection thread model, there are as many threads as there are clients currently connected, which has some disadvantages when server workload must scale to handle large numbers of connections. For example, thread creation and disposal becomes expensive. Also, each thread requires server and kernel resources, such as stack space. To accommodate a large number of simultaneous connections, the stack size per thread must be kept small, leading to a situation where it is either too small or the server consumes large amounts of memory. Exhaustion of other resources can occur as well, and scheduling overhead can become significant.
As of MySQL 5.5.16, MySQL Enterprise Edition includes a thread pool plugin that provides an alternative thread-handling model designed to reduce overhead and improve performance. It implements a thread pool that increases server performance by efficiently managing statement execution threads for large numbers of client connections. See Section 5.5.4, “MySQL Enterprise Thread Pool”.
To control and monitor how the server manages threads that handle client connections, several system and status variables are relevant. (See Section 5.1.4, “Server System Variables”, and Section 5.1.6, “Server Status Variables”.)
The thread cache has a size determined by the
thread_cache_size system
variable. The default value is 0 (no caching), which causes a
thread to be set up for each new connection and disposed of
when the connection terminates. Set
thread_cache_size to
N to enable
N inactive connection threads to be
cached. thread_cache_size can
be set at server startup or changed while the server runs. A
connection thread becomes inactive when the client connection
with which it was associated terminates.
To monitor the number of threads in the cache and how many
threads have been created because a thread could not be taken
from the cache, monitor the
Threads_cached and
Threads_created status
variables.
You can set max_connections
at server startup or at runtime to control the maximum number
of clients that can connect simultaneously.
When the thread stack is too small, this limits the complexity
of the SQL statements which the server can handle, the
recursion depth of stored procedures, and other
memory-consuming actions. To set a stack size of
N bytes for each thread, start the
server with
--thread_stack=.
N
The MySQL server maintains a host cache in memory that
contains information about clients: IP address, host name, and
error information. The server uses this cache for nonlocal TCP
connections. It does not use the cache for TCP connections
established using a loopback interface address
(127.0.0.1 or ::1), or
for connections established using a Unix socket file, named
pipe, or shared memory.
For each new client connection, the server uses the client IP address to check whether the client host name is in the host cache. If not, the server attempts to resolve the host name. First, it resolves the IP address to a host name and resolves that host name back to an IP address. Then it compares the result to the original IP address to ensure that they are the same. The server stores information about the result of this operation in the host cache. If the cache is full, the least recently used entry is discarded.
The server performs host name resolution using the thread-safe
gethostbyaddr_r() and
gethostbyname_r() calls if the operating
system supports them. Otherwise, the thread performing the
lookup locks a mutex and calls
gethostbyaddr() and
gethostbyname() instead. In this case, no
other thread can resolve host names that are not in the host
cache until the thread holding the mutex lock releases it.
The server uses the host cache for several purposes:
By caching the results of IP-to-host name lookups, the server avoids doing a DNS lookup for each client connection. Instead, for a given host, it needs to perform a lookup only for the first connection from that host.
The cache contains information about errors that occur
during the connection process. Some errors are considered
“blocking.” If too many of these occur
successively from a given host without a successful
connection, the server blocks further connections from
that host. The
max_connect_errors system
variable determines the number of permitted errors before
blocking occurs. See Section B.5.2.6, “Host 'host_name' is blocked”.
To unblock blocked hosts, flush the host cache by issuing a
FLUSH HOSTS
statement or executing a mysqladmin
flush-hosts command.
It is possible for a blocked host to become unblocked even
without FLUSH
HOSTS if activity from other hosts has occurred
since the last connection attempt from the blocked host. This
can occur because the server discards the least recently used
cache entry to make room for a new entry if the cache is full
when a connection arrives from a client IP not in the cache.
If the discarded entry is for a blocked host, that host
becomes unblocked.
The host cache is enabled by default. To disable it, start the
server with the
--skip-host-cache option.
To disable DNS host name lookups, start the server with the
--skip-name-resolve option. In
this case, the server uses only IP addresses and not host
names to match connecting hosts to rows in the MySQL grant
tables. Only accounts specified in those tables using IP
addresses can be used. (Be sure that an account exists that
specifies an IP address or you may not be able to connect.)
If you have a very slow DNS and many hosts, you might be able
to improve performance either by disabling DNS lookups with
--skip-name-resolve or by
increasing the HOST_CACHE_SIZE define
(default value: 128) and recompiling the server
To disallow TCP/IP connections entirely, start the server with
the --skip-networking option.
Some connection errors are not associated with TCP connections, occur very early in the connection process (even before an IP address is known), or are not specific to any particular IP address (such as out-of-memory conditions).
To measure performance, consider the following factors:
Whether you are measuring the speed of a single operation on a quiet system, or how a set of operations (a “workload”) works over a period of time. With simple tests, you usually test how changing one aspect (a configuration setting, the set of indexes on a table, the SQL clauses in a query) affects performance. Benchmarks are typically long-running and elaborate performance tests, where the results could dictate high-level choices such as hardware and storage configuration, or how soon to upgrade to a new MySQL version.
For benchmarking, sometimes you must simulate a heavy database workload to get an accurate picture.
Performance can vary depending on so many different factors that a difference of a few percentage points might not be a decisive victory. The results might shift the opposite way when you test in a different environment.
Certain MySQL features help or do not help performance
depending on the workload. For completeness, always test
performance with those features turned on and turned off. The
two most important features to try with each workload are the
MySQL query cache, and the
adaptive hash
index for InnoDB tables.
This section progresses from simple and direct measurement techniques that a single developer can do, to more complicated ones that require additional expertise to perform and interpret the results.
To measure the speed of a specific MySQL expression or function,
invoke the BENCHMARK() function
using the mysql client program. Its syntax is
BENCHMARK(.
The return value is always zero, but mysql
prints a line displaying approximately how long the statement
took to execute. For example:
loop_count,expression)
mysql> SELECT BENCHMARK(1000000,1+1);
+------------------------+
| BENCHMARK(1000000,1+1) |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.32 sec)
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.
The built-in MySQL functions are typically highly optimized, but
there may be some exceptions.
BENCHMARK() is an excellent tool
for finding out if some function is a problem for your queries.
This benchmark suite is meant to tell any user what operations a
given SQL implementation performs well or poorly. You can get a
good idea for how the benchmarks work by looking at the code and
results in the sql-bench directory in any
MySQL source distribution.
To use the benchmark suite, the following requirements must be satisfied:
The benchmark suite is provided with MySQL source distributions. You can either download a released distribution from http://dev.mysql.com/downloads/, or use the current development source tree. (See Section 2.9.3, “Installing MySQL Using a Development Source Tree”.)
The benchmark scripts are written in Perl and use the Perl
DBI module to access database servers, so DBI must be
installed. You also need the server-specific DBD drivers for
each of the servers you want to test. For example, to test
MySQL, PostgreSQL, and DB2, you must have the
DBD::mysql, DBD::Pg,
and DBD::DB2 modules installed. See
Section 2.13, “Perl Installation Notes”.
After you obtain a MySQL source distribution, you can find the
benchmark suite located in its sql-bench
directory. To run the benchmark tests, build MySQL, and then
change location into the sql-bench
directory and execute the run-all-tests
script:
shell>cd sql-benchshell>perl run-all-tests --server=server_name
server_name should be the name of one
of the supported servers. To get a list of all options and
supported servers, invoke this command:
shell> perl run-all-tests --help
The crash-me script also is located in the
sql-bench directory.
crash-me tries to determine what features a
database system supports and what its capabilities and
limitations are by actually running queries. For example, it
determines:
What data types are supported
How many indexes are supported
What functions are supported
How big a query can be
How big a VARCHAR column can
be
For more information about benchmark results, visit http://www.mysql.com/why-mysql/benchmarks/.
Benchmark your application and database to find out where the bottlenecks are. After fixing one bottleneck (or by replacing it with a “dummy” module), you can proceed to identify the next bottleneck. Even if the overall performance for your application currently is acceptable, you should at least make a plan for each bottleneck and decide how to solve it if someday you really need the extra performance.
For examples of portable benchmark programs, look at those in the MySQL benchmark suite. See Section 8.13.2, “The MySQL Benchmark Suite”. You can take any program from this suite and modify it for your own needs. By doing this, you can try different solutions to your problem and test which really is fastest for you.
Another free benchmark suite is the Open Source Database Benchmark, available at http://osdb.sourceforge.net/.
It is very common for a problem to occur only when the system is very heavily loaded. We have had many customers who contact us when they have a (tested) system in production and have encountered load problems. In most cases, performance problems turn out to be due to issues of basic database design (for example, table scans are not good under high load) or problems with the operating system or libraries. Most of the time, these problems would be much easier to fix if the systems were not already in production.
To avoid problems like this, benchmark your whole application under the worst possible load:
The mysqlslap program can be helpful for simulating a high load produced by multiple clients issuing queries simultaneously. See Section 4.5.7, “mysqlslap — Load Emulation Client”.
You can also try benchmarking packages such as SysBench and DBT2, available at https://launchpad.net/sysbench, and http://osdldbt.sourceforge.net/#dbt2.
These programs or packages can bring a system to its knees, so be sure to use them only on your development systems.
You can query the tables in the
performance_schema database to see real-time
information about the performance characteristics of your server
and the applications it is running. See
Chapter 22, MySQL Performance Schema for details.
As you monitor the performance of your MySQL server, examine the process list, which is the set of threads currently executing within the server. Process list information is available from these sources:
The SHOW [FULL] PROCESSLIST statement:
Section 13.7.5.30, “SHOW PROCESSLIST Syntax”
The SHOW PROFILE statement:
Section 13.7.5.32, “SHOW PROFILES Syntax”
The INFORMATION_SCHEMA
PROCESSLIST table:
Section 21.14, “The INFORMATION_SCHEMA PROCESSLIST Table”
The mysqladmin processlist command: Section 4.5.2, “mysqladmin — Client for Administering a MySQL Server”
You can always view information about your own threads. To view
information about threads being executed for other accounts, you
must have the PROCESS privilege.
Each process list entry contains several pieces of information:
Id is the connection identifier for the
client associated with the thread.
User and Host indicate
the account associated with the thread.
db is the default database for the thread,
or NULL if none is selected.
Command and State
indicate what the thread is doing.
Most states correspond to very quick operations. If a thread stays in a given state for many seconds, there might be a problem that needs to be investigated.
Time indicates how long the thread has been
in its current state. The thread's notion of the current time
may be altered in some cases: The thread can change the time
with SET
TIMESTAMP = . For a
thread running on a slave that is processing events from the
master, the thread time is set to the time found in the events
and thus reflects current time on the master and not the
slave.
value
Info contains the text of the statement
being executed by the thread, or NULL if it
is not executing one. By default, this value contains only the
first 100 characters of the statement. To see the complete
statements, use
SHOW FULL
PROCESSLIST.
The following sections list the possible
Command values, and State
values grouped by category. The meaning for some of these values
is self-evident. For others, additional description is provided.
A thread can have any of the following
Command values:
This is a thread on a master server for sending binary log contents to a slave server.
The thread is executing a change-user operation.
The thread is closing a prepared statement.
A replication slave is connected to its master.
A replication slave is connecting to its master.
The thread is executing a create-database operation.
This thread is internal to the server, not a thread that services a client connection.
The thread is generating debugging information.
The thread is a delayed-insert handler.
The thread is executing a drop-database operation.
The thread is executing a prepared statement.
The thread is fetching the results from executing a prepared statement.
The thread is retrieving information for table columns.
The thread is selecting a default database.
The thread is killing another thread.
The thread is retrieving long data in the result of executing a prepared statement.
The thread is handling a server-ping request.
The thread is preparing a prepared statement.
The thread is producing information about server threads.
The thread is executing a statement.
The thread is terminating.
The thread is flushing table, logs, or caches, or resetting status variable or replication server information.
The thread is registering a slave server.
The thread is resetting a prepared statement.
The thread is setting or resetting a client statement-execution option.
The thread is shutting down the server.
The thread is waiting for the client to send a new statement to it.
The thread is producing server-status information.
The thread is sending table contents to a slave server.
Unused.
The following list describes thread State
values that are associated with general query processing and not
more specialized activities such as replication. Many of these
are useful only for finding bugs in the server.
This occurs when the thread creates a table (including internal temporary tables), at the end of the function that creates the table. This state is used even if the table could not be created due to some error.
The thread is calculating a MyISAM table
key distributions (for example, for
ANALYZE TABLE).
The thread is checking whether the server has the required privileges to execute the statement.
The thread is performing a table check operation.
The thread has processed one command and is preparing to free memory and reset certain state variables.
The thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, verify that you do not have a full disk and that the disk is not in very heavy use.
The thread is converting an internal temporary table from a
MEMORY table to an on-disk
MyISAM table.
The thread is processing an ALTER
TABLE statement. This state occurs after the table
with the new structure has been created but before rows are
copied into it.
If a statement has different ORDER BY and
GROUP BY criteria, the rows are sorted by
group and copied to a temporary table.
The server is copying to a temporary table in memory.
The server is copying to a temporary table on disk. The temporary result set has become too large (see Section 8.4.4, “Internal Temporary Table Use in MySQL”). Consequently, the thread is changing the temporary table from in-memory to disk-based format to save memory.
The thread is processing ALTER TABLE ... ENABLE
KEYS for a MyISAM table.
The thread is processing a
SELECT that is resolved using
an internal temporary table.
The thread is creating a table. This includes creation of temporary tables.
The thread is creating a temporary table in memory or on
disk. If the table is created in memory but later is
converted to an on-disk table, the state during that
operation will be Copying to tmp table on
disk.
The server is executing the first part of a multiple-table delete. It is deleting only from the first table, and saving columns and offsets to be used for deleting from the other (reference) tables.
deleting from reference tables
The server is executing the second part of a multiple-table delete and deleting the matched rows from the other tables.
The thread is processing an ALTER TABLE ... DISCARD
TABLESPACE or ALTER TABLE ... IMPORT
TABLESPACE statement.
This occurs at the end but before the cleanup of
ALTER TABLE,
CREATE VIEW,
DELETE,
INSERT,
SELECT, or
UPDATE statements.
The thread has begun executing a statement.
The thread is executing statements in the value of the
init_command system variable.
The thread has executed a command. Some freeing of items
done during this state involves the query cache. This state
is usually followed by cleaning up.
The thread is executing
FLUSH
TABLES and is waiting for all threads to close
their tables. This state was removed in MySQL 5.5.6;
Waiting for table flush is used instead.
The server is preparing to perform a natural-language full-text search.
This occurs before the initialization of
ALTER TABLE,
DELETE,
INSERT,
SELECT, or
UPDATE statements. Actions
taken by the server in this state include flushing the
binary log, the InnoDB log, and some
query cache cleanup operations.
For the end state, the following
operations could be happening:
Removing query cache entries after data in a table is changed
Writing an event to the binary log
Freeing memory buffers, including for blobs
Someone has sent a KILL
statement to the thread and it should abort next time it
checks the kill flag. The flag is checked in each major loop
in MySQL, but in some cases it might still take a short time
for the thread to die. If the thread is locked by some other
thread, the kill takes effect as soon as the other thread
releases its lock.
The query is locked by another query.
In MySQL 5.5.3, this state was removed because it was
equivalent to the Table lock state.
The thread is writing a statement to the slow-query log.
The initial state for a connection thread until the client has been authenticated successfully.
The server is enabling or disabling a table index.
This state is used for the SHOW
PROCESSLIST state.
The thread is trying to open a table. This is should be very
fast procedure, unless something prevents opening. For
example, an ALTER TABLE or a
LOCK
TABLE statement can prevent opening a table until
the statement is finished. It is also worth checking that
your table_open_cache value
is large enough.
The server is performing initial optimizations for a query.
This state occurs during query optimization.
The thread is removing unneeded relay log files.
This state occurs after processing a query but before the
freeing items state.
The server is reading a packet from the network.
The query was using
SELECT
DISTINCT in such a way that MySQL could not
optimize away the distinct operation at an early stage.
Because of this, MySQL requires an extra stage to remove all
duplicated rows before sending the result to the client.
The thread is removing an internal temporary table after
processing a SELECT
statement. This state is not used if no temporary table was
created.
The thread is renaming a table.
The thread is processing an ALTER
TABLE statement, has created the new table, and is
renaming it to replace the original table.
The thread got a lock for the table, but noticed after getting the lock that the underlying table structure changed. It has freed the lock, closed the table, and is trying to reopen it.
The repair code is using a sort to create indexes.
The thread has completed a multi-threaded repair for a
MyISAM table.
The repair code is using creating keys one by one through
the key cache. This is much slower than Repair by
sorting.
The thread is rolling back a transaction.
For MyISAM table operations such as
repair or analysis, the thread is saving the new table state
to the .MYI file header. State includes
information such as number of rows, the
AUTO_INCREMENT counter, and key
distributions.
The thread is doing a first phase to find all matching rows
before updating them. This has to be done if the
UPDATE is changing the index
that is used to find the involved rows.
Sending data
The thread is reading and processing rows for a
SELECT statement, and sending
data to the client. Because operations occurring during this
state tend to perform large amounts of disk access (reads),
it is often the longest-running state over the lifetime of a
given query.
The thread is beginning an ALTER
TABLE operation.
The thread is doing a sort to satisfy a GROUP
BY.
The thread is doing a sort to satisfy an ORDER
BY.
The thread is sorting index pages for more efficient access
during a MyISAM table optimization
operation.
For a SELECT statement, this
is similar to Creating sort index, but
for nontemporary tables.
The server is calculating statistics to develop a query execution plan. If a thread is in this state for a long time, the server is probably disk-bound performing other work.
The thread has called mysql_lock_tables()
and the thread state has not been updated since. This is a
very general state that can occur for many reasons.
For example, the thread is going to request or is waiting
for an internal or external system lock for the table. This
can occur when InnoDB waits for
a table-level lock during execution of
LOCK TABLES. If this state is
being caused by requests for external locks and you are not
using multiple mysqld servers that are
accessing the same MyISAM
tables, you can disable external system locks with the
--skip-external-locking
option. However, external locking is disabled by default, so
it is likely that this option will have no effect. For
SHOW PROFILE, this state
means the thread is requesting the lock (not waiting for
it).
The next thread state after System lock.
After acquiring a system lock, the thread is going to
request an internal table lock (a
THR_LOCK lock).
For more information about table lock indicators, see Section 8.11.1, “Internal Locking Methods”.
This state was replaced in MySQL 5.5.6 with Waiting
for table level lock.
The thread is getting ready to start updating the table.
The thread is searching for rows to update and is updating them.
The server is executing the first part of a multiple-table update. It is updating only the first table, and saving columns and offsets to be used for updating the other (reference) tables.
The server is executing the second part of a multiple-table update and updating the matched rows from the other tables.
The thread is going to request or is waiting for an advisory
lock requested with a
GET_LOCK() call. For
SHOW PROFILE, this state
means the thread is requesting the lock (not waiting for
it).
The thread has invoked a
SLEEP() call.
Waiting for all running commits to finish
A statement that causes an explicit or implicit commit is
waiting for release of a read lock. This state was removed
in MySQL 5.5.8; Waiting for commit lock
is used instead.
A statement that causes an explicit or implicit commit is
waiting for release of a read lock or
FLUSH TABLES WITH
READ LOCK is waiting for a commit lock. This state
was added in MySQL 5.5.8.
FLUSH TABLES WITH
READ LOCK is waiting for a global read lock or the
global read_only system
variable is being set.
Waiting for release of readlock
The thread is waiting for a global read lock obtained by
another thread (with
FLUSH TABLES WITH
READ LOCK) to be released. This state was removed
in MySQL 5.5.8; Waiting for global read
lock or Waiting for commit lock
are used instead.
Waiting for tables, Waiting for
table
The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.
This notification takes place if another thread has used
FLUSH
TABLES or one of the following statements on the
table in question: FLUSH TABLES
,
tbl_nameALTER TABLE,
RENAME TABLE,
REPAIR TABLE,
ANALYZE TABLE, or
OPTIMIZE TABLE.
Waiting for table was was removed in
MySQL 5.5.6; Waiting for table flush is
used instead.
The thread is executing
FLUSH
TABLES and is waiting for all threads to close
their tables, or the thread got a notification that the
underlying structure for a table has changed and it needs to
reopen the table to get the new structure. However, to
reopen the table, it must wait until all other threads have
closed the table in question.
This notification takes place if another thread has used
FLUSH
TABLES or one of the following statements on the
table in question: FLUSH TABLES
,
tbl_nameALTER TABLE,
RENAME TABLE,
REPAIR TABLE,
ANALYZE TABLE, or
OPTIMIZE TABLE.
In MySQL 5.5.6, Waiting for table flush
replaces the Flushing tables and
Waiting for table states.
The server is waiting to acquire a
THR_LOCK lock or a lock from the metadata
locking subsystem, where
lock_type indicates the type of
lock.
This state indicates a wait for a
THR_LOCK:
Waiting for table level lock
(replaces Table lock in MySQL 5.5.6)
These states indicate a wait for a metadata lock:
Waiting for event metadata lock
(added in MySQL 5.5.8)
Waiting for global metadata lock
(replaced by Waiting for global read
lock in MySQL 5.5.8)
Waiting for global read lock (added
in MySQL 5.5.8)
Waiting for schema metadata lock
Waiting for stored function metadata
lock
Waiting for stored procedure metadata
lock
Waiting for table metadata lock
Waiting for trigger metadata lock
(added in MySQL 5.5.8)
For information about table lock indicators, see Section 8.11.1, “Internal Locking Methods”. For information about metadata locking, see Section 8.11.4, “Metadata Locking”.
A generic state in which the thread is waiting for a condition to become true. No specific state information is available.
The thread has issued a
FLUSH TABLES WITH
READ LOCK statement to obtain a global read lock
and is waiting to obtain the lock. This state was removed in
MySQL 5.5.8; Waiting for global read lock
is used instead.
The server is writing a packet to the network.
These thread states are associated with processing for
DELAYED inserts (see
Section 13.2.5.2, “INSERT DELAYED Syntax”). Some states are associated
with connection threads that process INSERT
DELAYED statements from clients. Other states are
associated with delayed-insert handler threads that insert the
rows. There is a delayed-insert handler thread for each table
for which INSERT DELAYED
statements are issued.
States associated with a connection thread that processes an
INSERT DELAYED statement from the
client:
The thread is preparing to feed rows to the delayed-insert handler thread.
The thread is creating a handler for
DELAYED inserts.
This occurs before the allocating local
table state and after the waiting for
handler lock state, when the connection thread
gets access to the delayed-insert handler thread.
This occurs after the waiting for handler
open state. The delayed-insert handler thread has
signaled that it has ended its initialization phase, which
includes opening the table for delayed inserts.
The thread is adding a new row to the list of rows that the delayed-insert handler thread must insert.
This occurs during the initialization phase when the thread is trying to find the delayed-insert handler thread for the table, and before attempting to gain access to the list of delayed-insert threads.
An INSERT DELAYED handler has
processed all pending inserts and is waiting for new ones.
This occurs before the allocating local
table state when the connection thread waits for
access to the delayed-insert handler thread.
This occurs after the Creating delayed
handler state and before the got old
table state. The delayed-insert handler thread has
just been started, and the connection thread is waiting for
it to initialize.
States associated with a delayed-insert handler thread that inserts the rows:
The state that occurs just before inserting rows into the table.
After inserting a number of rows, the delayed-insert thread sleeps to let other threads do work.
A delayed-insert handler is trying to get a lock for the table to insert rows.
A delayed-insert handler is waiting for a connection thread
to add rows to the queue (see storing row into
queue).
These thread states are associated with the query cache (see Section 8.10.3, “The MySQL Query Cache”).
checking privileges on cached query
The server is checking whether the user has privileges to access a cached query result.
checking query cache for query
The server is checking whether the current query is present in the query cache.
invalidating query cache entries
Query cache entries are being marked invalid because the underlying tables have changed.
sending cached result to client
The server is taking the result of a query from the query cache and sending it to the client.
The server is storing the result of a query in the query cache.
This state occurs while a session is waiting to take the
query cache lock. This can happen for any statement that
needs to perform some query cache operation, such as an
INSERT or
DELETE that invalidates the
query cache, a SELECT that
looks for a cached entry,
RESET QUERY
CACHE, and so forth.
The following list shows the most common states you may see in
the State column for the master's
Binlog Dump thread. If you see no
Binlog Dump threads on a master server, this
means that replication is not running—that is, that no
slaves are currently connected.
Finished reading one binlog; switching to next
binlog
The thread has finished reading a binary log file and is opening the next one to send to the slave.
Master has sent all binlog to slave; waiting for
binlog to be updated
The thread has read all outstanding updates from the binary logs and sent them to the slave. The thread is now idle, waiting for new events to appear in the binary log resulting from new updates occurring on the master.
Binary logs consist of events, where an event is usually an update plus some other information. The thread has read an event from the binary log and is now sending it to the slave.
Waiting to finalize termination
A very brief state that occurs as the thread is stopping.
The following list shows the most common states you see in the
State column for a slave server I/O thread.
This state also appears in the Slave_IO_State
column displayed by SHOW SLAVE
STATUS, so you can get a good view of what is
happening by using that statement.
A state that occurs very briefly, after the connection to the master is established.
The thread is attempting to connect to the master.
Queueing master event to the relay log
The thread has read an event and is copying it to the relay log so that the SQL thread can process it.
Reconnecting after a failed binlog dump
request
The thread is trying to reconnect to the master.
Reconnecting after a failed master event
read
The thread is trying to reconnect to the master. When
connection is established again, the state becomes
Waiting for master to send event.
A state that occurs very briefly after the connection to the master is established.
A state that occurs very briefly, after the connection to the master is established. The thread sends to the master a request for the contents of its binary logs, starting from the requested binary log file name and position.
Waiting for master to send event
The thread has connected to the master and is waiting for
binary log events to arrive. This can last for a long time
if the master is idle. If the wait lasts for
slave_net_timeout seconds,
a timeout occurs. At that point, the thread considers the
connection to be broken and makes an attempt to reconnect.
The initial state before Connecting to
master.
Waiting for slave mutex on exit
A state that occurs briefly as the thread is stopping.
Waiting for the slave SQL thread to free enough
relay log space
You are using a nonzero
relay_log_space_limit
value, and the relay logs have grown large enough that their
combined size exceeds this value. The I/O thread is waiting
until the SQL thread frees enough space by processing relay
log contents so that it can delete some relay log files.
Waiting to reconnect after a failed binlog dump
request
If the binary log dump request failed (due to
disconnection), the thread goes into this state while it
sleeps, then tries to reconnect periodically. The interval
between retries can be specified using the
CHANGE MASTER TO statement.
Waiting to reconnect after a failed master event
read
An error occurred while reading (due to disconnection). The
thread is sleeping for the number of seconds set by the
CHANGE MASTER TO statement
(default 60) before attempting to reconnect.
The following list shows the most common states you may see in
the State column for a slave server SQL
thread:
Waiting for the next event in relay log
The initial state before Reading event from the
relay log.
Reading event from the relay log
The thread has read an event from the relay log so that the event can be processed.
The thread is executing a
LOAD DATA
INFILE statement and is creating a temporary file
containing the data from which the slave will read rows.
Slave has read all relay log; waiting for the slave
I/O thread to update it
The thread has processed all events in the relay log files, and is now waiting for the I/O thread to write new events to the relay log.
Waiting for slave mutex on exit
A very brief state that occurs as the thread is stopping.
The State column for the I/O thread may also
show the text of a statement. This indicates that the thread has
read an event from the relay log, extracted the statement from
it, and is executing it.
These thread states occur on a replication slave but are associated with connection threads, not with the I/O or SQL threads.
The thread is processing a CHANGE
MASTER TO statement.
The thread is processing a STOP SLAVE
statement.
This state occurs after Creating table from master
dump.
Reading master dump table data
This state occurs after Opening master dump
table.
Rebuilding the index on master dump table
This state occurs after Reading master dump table
data.
The thread is processing events for binary logging.
Processing events from schema table
The thread is doing the work of schema replication.
Syncing ndb table schema operation and
binlog
This is used to have a correct binary log of schema operations for NDB.
Waiting for allowed to take ndbcluster global
schema lock
The thread is waiting for permission to take a global schema lock. Added in MySQL Cluster NDB 7.2.
Waiting for event from ndbcluster
The server is acting as an SQL node in a MySQL Cluster, and is connected to a cluster management node.
Waiting for ndbcluster binlog update to reach
current position
Waiting for ndbcluster global schema lock
The thread is waiting for a global schema lock held by another thread to be released. Added in MySQL Cluster NDB 7.2.
The thread is waiting for a schema epoch (that is, a global checkpoint).
These states occur for the Event Scheduler thread, threads that are created to execute scheduled events, or threads that terminate the scheduler.
The scheduler thread or a thread that was executing an event is terminating and is about to end.
The scheduler thread or a thread that will execute an event has been initialized.
The scheduler has a nonempty event queue but the next activation is in the future.
The thread issued SET GLOBAL
event_scheduler=OFF and is waiting for the
scheduler to stop.
The scheduler's event queue is empty and it is sleeping.