What is the meaning of filtered in MySQL explain?

Question

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.

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.