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 queries will show 100, and some others show 18 or anything 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 is the main point that we can conclude from this value?

Is it saying that the column filtered only 18%? Or is it saying that the lower the score, the better the index or query?

I'm using MySQL 5.7.

1 Answer 1


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 table a using the search-key. It estimates to get 174 rows out of using that index, which is the number in rows. 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 in filtered.

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 and filtered=0.1% could become an index lookup with rows=1 and filtered=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 (especially type, key and extra) to optimize your query. It can e.g. be better to get rid of a filesort (e.g. by using an index that satisfies the order by), even if it results in a lower filtered value. And a better type can result in a huge performance improvement, even if it might not change or even lower filtered. In the example above with filtered=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 at filtered 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.

  • 1
    Thanks for the explaining. It's explain a lot for me. I think it's useful for maintaining and selecting the good index Feb 15, 2017 at 1:16
  • @ImanTumorang I added a remark and an examples about that: don't take that value too seriously. You can optimize your query by just looking at type and extra (which is an art on its own); you could live without filtered, but not without type.
    – Solarflare
    Feb 15, 2017 at 11:35
  • Okay then. I got it. I already read it in the Mysql Docs, how they affect the performance. Thanks for your explanation :D Feb 16, 2017 at 13:30
  • 3
    Another tip: The filtered calculation is skipped fo the last table joined. that is, it will show 100% even if there are in fact conditions that will filter out some of the rows examined. The rationale is that it costs something to estimate the filtering factor, and this won't affect the query execution plan if it's on the last table, so they default to skipping the calculation. Nov 28, 2019 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.