EXPLAIN
Output Format
648
SELECT * FROM
tbl_name
WHERE
key_column
IN (10,20,30);
SELECT * FROM
tbl_name
WHERE
key_part1
= 10 AND
key_part2
IN (10,20,30);
•
index
[648]
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 satisify 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.
•
ALL
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
[646]
, 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.
EXPLAIN
Extra Information
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
.
•
const row not found
For a query such as
SELECT ... FROM tbl_name
, the table was empty.
•
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
[646]
(and
system
[646]
) tables and notice that the
WHERE
clause is
always false.
•
No matching min/max row
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
