What does the filtered value mean in the context of MySQL EXPLAIN EXTENDED?

What does it mean filteredin the context of MySQL EXPLAIN EXTENDED?

MySQL docs :

filtered (JSON name: filtered)

The filtered column indicates the approximate percentage of table rows that will be filtered by the condition of the table. That is, the rows show the estimated number of rows scanned, and the × filter / 100 rows show the number of rows that will be joined with the previous tables. Before MySQL 5.7.3, this column is displayed if you are using EXPLAIN EXTENDED. Starting with MySQL 5.7.3, advanced output is enabled by default, and the EXTENDED keyword is not required.

But I still don’t know. For example, 100.00does that mean it's good? What does it mean if I have 43.61? This is better? Worse? Should I try to get 100.00?

Is this for informational purposes only? Let's say I have two similar queries that combine on different keys, but produce the same rows and values ​​based on the SQL ER structure and business logic. One request reports 100.00 for all connections, and the other says 43.61 for one of the connections and 100.00 for the others. What should I 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

https://dba.stackexchange.com/a/164360

+4
source share
1 answer

, , , , , 100% , . , , mysql, 100, .

"", , . , , , .

2 . , , - 1 * 512 .

(eq_ref), , 1 , . 1 * 512, 43%. , , , .

, , , , , mysql 512 .

, , , , - format = json, , rows_examined_per_scan rows_produced_per_join.

, :

explain format=json <your query>

, , .

0

Source: https://habr.com/ru/post/1660525/


All Articles