CODEWITHSUNDEEP
mysql
Chris Henry
Chris Henry

Reputation: 12010

What does the filtered column of a EXPLAIN'ed query mean in MySQL?

mysql> EXPLAIN EXTENDED SELECT * FROM table WHERE column = 1 LIMIT 10;
+----+-------------+----------+------+---------------+--------------+---------+-------+--------+----------+-------+
| id | select_type | table    | type | possible_keys | key          | key_len | ref   | rows   | filtered | Extra |
+----+-------------+----------+------+---------------+--------------+---------+-------+--------+----------+-------+
|  1 | SIMPLE      | table    | ref  | column        | column       | 1       | const | 341878 |   100.00 |       |
+----+-------------+----------+------+---------------+--------------+---------+-------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

What does the filtered column mean, and should the number be high or low? Yes, I've read the docs, but I don't quite understand what the number indicates or what values are considered desirable.

Upvotes: 4

Views: 4078

Answers (1)

the_void
the_void

Reputation: 5538

The filtered column is an estimated percentage that specifies the number of rows that will be joined with the previous table. In your case it's 100%, i.e. all rows.

The rows, as you presumably know, is an estimation of the number of rows examined by the query, so rows x filtered / 100 will be the number of joins that have to be made (the number of rows left after applying the table condition).

For additional info, see What's New in MySQL Optimizer - What's new in MySQL Query Optimizer (slide 10).

EDIT:

There is an Appendix in the High performance MySQL (2nd edition) book which suggests that the optimizer currently uses this estimate (filtered) only for the ALL, index, range and index_merge access methods.

Upvotes: 5

Related Questions