As described here in the MySQL docs:
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. Before MySQL 5.7.3, this column is displayed if you use EXPLAIN EXTENDED. As of MySQL 5.7.3, extended output is enabled by default and the EXTENDED keyword is unnecessary.
I still don't get it. What is the meaning of "filtered" here? What information we can get from this column?
For example, when I start querying, some query will show 100, and some others show 18 or any lower than 100.
+-------------+-------+--------+---------+---------+------+----------+
| select_type | table | type | key | key_len | rows | filtered |
+-------------+-------+--------+---------+---------+------+----------+
| PRIMARY | a | range | search | 4 | 174 | 18.00 | <--
| PRIMARY | b | eq_ref | PRIMARY | 4 | 1 | 100.00 |
| PRIMARY | c | ALL | PRIMARY | 4 | 1 | 100.00 |
What the main point we can conclude from this value?
Is it says that, the column filtered only 18%? Or if the more lower the score, the more good the index/query ?
I'm using MySQL 5.7
Best Answer
To filter here means to apply a condition on a set of rows that were selected by a
type
-search as potential rows, and to only keep rows that fulfill the condition:MySQL will first try to use an index, e.g. do a
range
scan on your tablea
using thesearch
-key. It estimates to get 174 rows out of using that index, which is the number inrows
. This step is not yet called filtering.After that, these 174 rows have to be checked against additional conditions (usually in your
where
-clause). MySQL now estimated that only 32 rows, so 18% of these 174 rows, will remain after that filter has been applied. This 18% is the value infiltered
.While it is obviously better to have 32 rows instead of 174 (if you e.g. have to later
join
them with another table), a "perfect" index would have given you these 32 rows directly from the initial search, saving you the time to look at and filter out 82% of all potential rows.So a low value might indicate that there could be a better index: e.g. a full table scan with
rows=1000
andfiltered=0.1%
could become an index lookup withrows=1
andfiltered=100%
if you add a good index.On the other hand, you can very well completely ignore this
filtered
-value (which is in most cases a really bad estimate anyway), and focus on the other more important columns (especiallytype
,key
andextra
) to optimize your query. It can e.g. be better to get rid of afilesort
(e.g. by using an index that satisfies theorder by
), even if it results in a lowerfiltered
value. And a bettertype
can result in a huge performance improvement, even if it might not change or even lowerfiltered
. In the example above withfiltered=0.1%
,type=all
would have already be enough to indicate that you might be able to improve that query by adding an index, without looking atfiltered
at all.So don't take that value too seriously: neither does
100
mean your indexes are good, nor does a lower value necessarily indicate bad indexes.type
is a much better indicator for that.