|
create table t0 (a int) engine=myisam;
|
INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
create table t1 (a int) engine=myisam;
|
INSERT INTO t1 select * from t0;
|
MariaDB [j2]> analyze (select * from t1 A where a<5) union (select * from t1 B where a in (2,3));
|
+------+--------------+------------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+--------------+------------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
|
| 1 | PRIMARY | A | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 50.00 | Using where |
|
| 2 | UNION | B | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | 100.00 | 20.00 | Using where |
|
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | 5.00 | NULL | NULL | |
|
+------+--------------+------------+------+---------------+------+---------+------+------+--------+----------+------------+-------------+
|
So far, ok. Note that we can see how many rows were in the union's result: r_rows=5. Now, let's try to get even more info and run ANALYZE FORMAT=JSON:
MariaDB [j2]> analyze format=json (select * from t1 A where a<5) union (select * from t1 B where a in (2,3))\G
|
*************************** 1. row ***************************
|
ANALYZE: {
|
"query_block": {
|
"union_result": {
|
"table_name": "<union1,2>",
|
"access_type": "ALL",
|
"query_specifications": [
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.584,
|
"table": {
|
"table_name": "A",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 10,
|
"r_rows": 10,
|
"r_total_time_ms": 0.4224,
|
"filtered": 100,
|
"r_filtered": 50,
|
"attached_condition": "(A.a < 5)"
|
}
|
}
|
},
|
{
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 1,
|
"r_total_time_ms": 0.382,
|
"table": {
|
"table_name": "B",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 10,
|
"r_rows": 10,
|
"r_total_time_ms": 0.3193,
|
"filtered": 100,
|
"r_filtered": 20,
|
"attached_condition": "(B.a in (2,3))"
|
}
|
}
|
}
|
]
|
}
|
}
|
}
|
we can't see r_rows for union result.
|