21

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

30

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 – Iman Tumorang Feb 15 '17 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 '17 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 – Iman Tumorang Feb 16 '17 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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