Example #1
 
Customers who have ordered more than 1M goods.
 
ANALYZE FORMAT=JSON
SELECT CONT(*)
FROM customer
WHERE
 (SELECT SUM(o_totalprice) FROM orders WHERE
o_custkey=c_custkey) > 1000*1000;
 
The query takes 40 seconds over cold cache
 
EXPLAIN: {
 "query_block": {
 "select_id": 1,
 "r_loops": 1,
 "r_total_time_ms": 39872,
 "table": {
 "table_name": "customer",
 "access_type": "index",
 "key": "i_c_nationkey",
 "key_length": "5",
 "used_key_parts": ["c_nationkey"],
 "r_loops": 1,
 "rows": 150303,
 "r_rows": 150000,
 "r_total_time_ms": 270.3,
 "filtered": 100,
 "r_filtered": 60.691,
 "attached_condition": "((subquery#2) > ((1000 *
1000)))",
 "using_index": true
 },
 "subqueries": [
 {
 "query_block": {
 "select_id": 2,
 "r_loops": 150000,
 "r_total_time_ms": 39531,
 "table": {
 "table_name": "orders",
 "access_type": "ref",
 "possible_keys": ["i_o_custkey"],
 "key": "i_o_custkey",
 "key_length": "5",
 "used_key_parts": ["o_custkey"],
 "ref": ["dbt3sf1.customer.c_custkey"],
 "r_loops": 150000,
 "rows": 7,
 "r_rows": 10,
 "r_total_time_ms": 39208,
 "filtered": 100,
 "r_filtered": 100
 }
 }
 }
 ]
 }
}
ANALYZE shows that 39.2 seconds were spent in the subquery,
which was executed 150K times (for every row of outer
table).
 

 
 
 
 
 
 
 

URL: https://mariadb.com/kb/en/analyze-formatjson-examples/