Databases Reference
In-Depth Information
probably not have to actually read every row it predicts it will. MySQL also doesn't
know anything about the operating system or hardware caches.
The filtered Column
This column is new in MySQL 5.1 and appears when you use
EXPLAIN EXTENDED
. It
shows a pessimistic estimate of the percentage of rows that will satisfy some condition
on the table, such as a
WHERE
clause or a join condition. If you multiply the
rows
column
by this percentage, you will see the number of rows MySQL estimates it will join with
the previous tables in the query plan. At the time of this writing, the optimizer uses this
estimate only for the
ALL
,
index
,
range
, and
index_merge
access methods.
To illustrate this column's output, we created a table as follows:
CREATE TABLE t1 (
id INT NOT NULL AUTO_INCREMENT,
filler char(200),
PRIMARY KEY(id)
);
We then inserted 1,000 rows into this table, with random text in the
filler
column.
Its purpose is to prevent MySQL from using a covering index for the query we're about
to run:
mysql>
EXPLAIN EXTENDED SELECT * FROM t1 WHERE id < 500\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1000
filtered: 49.40
Extra: Using where
MySQL could use a range access to retrieve all rows with IDs less than 500 from the
table, but it won't because that would eliminate only about half the rows. It thinks a
table scan is less expensive. As a result, it uses a table scan and a
WHERE
clause to filter
out rows. It knows how many rows the
WHERE
clause will remove from the result, because
of the range access cost estimates. That's why the 49.40% value appears in the
fil
tered
column.
The Extra Column
This column contains extra information that doesn't fit into other columns. The
MySQL manual documents most of the many values that can appear here; we have
referred to many of them throughout this topic.
