Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.1.0, 10.2(EOL), 10.3(EOL)
Description
MariaDB [test]> create table t1 (a int, b int) engine=MyISAM; |
Query OK, 0 rows affected (0.24 sec) |
|
MariaDB [test]> insert into t1 values (1,100),(2,200),(3,300),(4,400); |
Query OK, 4 rows affected (0.00 sec) |
Records: 4 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> analyze select * from t1 where b > 300; |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 4 | 4 | 100.00 | 25.00 | Using where | |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ |
1 row in set (0.00 sec) |
|
MariaDB [test]> analyze select * from t1 where b > 300 order by b; |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-----------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-----------------------------+ |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 4 | 1 | 100.00 | 100.00 | Using where; Using filesort | |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-----------------------------+ |
1 row in set (0.00 sec) |
Same with a bigger number of rows:
MariaDB [test]> insert into t1 select * from t1; |
Query OK, 4 rows affected (0.00 sec) |
Records: 4 Duplicates: 0 Warnings: 0
|
...
|
...
|
|
MariaDB [test]> analyze select * from t1 where b > 300; |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 4096 | 4096 | 100.00 | 25.00 | Using where | |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+ |
1 row in set (0.01 sec) |
|
MariaDB [test]> analyze select * from t1 where b > 300 order by b; |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-----------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-----------------------------+ |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 4096 | 1024 | 100.00 | 100.00 | Using where; Using filesort | |
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+-----------------------------+ |
1 row in set (0.00 sec) |
Maybe it's by design, but then it needs to be documented, now KB just says
It shows which fraction of rows was left after applying the WHERE condition.
ANALYZE FORMAT=JSON produces this:
MariaDB [j1a]> analyze format=json select * from t1 where b > 300 order by b\G
*************************** 1. row ***************************
ANALYZE: {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.1379,
"read_sorted_file": {
"r_rows": 1,
"filesort": {
"r_loops": 1,
"r_used_priority_queue": false,
"r_output_rows": 1,
"r_buffer_size": "308",
"table": {
"table_name": "t1",
"access_type": "ALL",
"r_loops": 1,
"rows": 4,
"r_rows": 4,
"r_total_time_ms": 0.0073,
"filtered": 100,
"r_filtered": 0.25,
"attached_condition": "(t1.b > 300)"
}
}
}
}
}
Note that r_filtered near attached_condition shows the right value.