Details
Minor
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.
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
We could still fix the tabular output, too.
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.