What does filtered mean in the context of MySQL EXPLAIN EXTENDED?
MySQL Docs states:
filtered (JSON name: filtered)
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.
But I am still not having a clue. For example, does 100.00 means it's good? What does it mean if I have 43.61? Is it better? Worse? Should I try to have 100.00?
Is it just for information purposes only? Say I have two similar queries that join on different keys but produce the same rows and values based on SQL ER structure and business logic. One query reports 100.00 for all joins and the other says 43.61 for one of the joins and 100.00 for others. What am I to learn from this number?
Example:
+-------------+-------+--------+---------+---------+------+----------+
| select_type | table | type | key | key_len | rows | filtered |
+-------------+-------+--------+---------+---------+------+----------+
| PRIMARY | job | range | status | 122 | 512 | 100.00 |
| PRIMARY | user | eq_ref | PRIMARY | 4 | 1 | 100.00 |
| PRIMARY | item | eq_ref | PRIMARY | 4 | 1 | -> 43.61 | <--
+-------------+-------+--------+---------+---------+------+----------+
Also see
We don't have your query to work with, and this really isn't the right forum for this question, but to specifically answer your question, you want your filtering to return 100% of the rows if possible. Of course if you look at the manual, depending on the version of mysql, filtered could be a number larger than 100, so in that case more like a ratio.
In the initial 'range' optimization, where an index on job was used, you did not have any rows filtered. So this is optimal because the index completely covered the criteria, and the resulting data did not need to be reduced.
In step 2 those jobs were joined to a user. There was only 1 user that matched, so your intermediary result is 1 user * 512 jobs.
In the final join (eq_ref) where you are joining to your item table, there is 1 item rows that qualify. But now, some filtering is being applied and of the 1 * 512 rows, only 43% are ultimately being returned. That could be improved probably with an index, although that might not be worth the overhead.
This is a small result set, so it's not really a big concern, but what that does tell you is that mysql had to go through the 512 rows and do some additional reduction by filtering.
Again without the query, and some specific understanding of the data it is hard to say, but I can give you a tip that may help you attain additional insight is to utilize the format=json parameter which will show you some additional information, particularly rows_examined_per_scan and rows_produced_per_join.
Specifically you should try this:
If you want to explore it further, you could update the question with that data, but also the query would be helpful.