1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
|
CREATE TABLE t1(i INT, j INT);
INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(4,4);
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
#
# Bug#34302461: EXPLAIN cost and row estimates are wrong for UNION
#
EXPLAIN FORMAT=TREE SELECT i FROM t1 UNION SELECT 7 ORDER BY i;
EXPLAIN
-> Sort: i (cost=6.08..6.08 rows=6)
-> Table scan on <union temporary> (cost=1.78..3.93 rows=6)
-> Union materialize with deduplication (cost=1.35..1.35 rows=6)
-> Table scan on t1 (cost=0.75 rows=5)
-> Rows fetched before execution (cost=0..0 rows=1)
EXPLAIN FORMAT=TREE SELECT * FROM (SELECT i FROM t1 UNION SELECT 7) d1
ORDER BY i;
EXPLAIN
-> Sort: d1.i (cost=6.08..6.08 rows=6)
-> Table scan on d1 (cost=1.78..3.93 rows=6)
-> Union materialize with deduplication (cost=1.35..1.35 rows=6)
-> Table scan on t1 (cost=0.75 rows=5)
-> Rows fetched before execution (cost=0..0 rows=1)
EXPLAIN FORMAT=TREE SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY i;
EXPLAIN
-> Sort: i (cost=9.45..9.45 rows=10)
-> Table scan on <union temporary> (cost=2.76..5.12 rows=10)
-> Union materialize with deduplication (cost=2.5..2.5 rows=10)
-> Table scan on t1 (cost=0.75 rows=5)
-> Table scan on t1 (cost=0.75 rows=5)
EXPLAIN FORMAT=TREE SELECT * FROM (SELECT * FROM t1 UNION SELECT * FROM t1)
d1 ORDER BY i;
EXPLAIN
-> Sort: d1.i (cost=9.45..9.45 rows=10)
-> Table scan on d1 (cost=2.76..5.12 rows=10)
-> Union materialize with deduplication (cost=2.5..2.5 rows=10)
-> Table scan on t1 (cost=0.75 rows=5)
-> Table scan on t1 (cost=0.75 rows=5)
#
# Bug#33834146: 'EXPLAIN ANALYZE' cost + elapsed time not cumulative
# when using materialization
#
EXPLAIN FORMAT=TREE INSERT INTO t1 SELECT MAX(i), 3 FROM
(SELECT * FROM t1 ORDER BY j LIMIT 5) AS d1;
EXPLAIN
-> Insert into t1
-> Aggregate: max(t1.i) (cost=7.38..7.38 rows=1)
-> Table scan on <temporary> (cost=4.83..6.88 rows=5)
-> Temporary table (cost=4.31..4.31 rows=5)
-> Table scan on d1 (cost=1.76..3.81 rows=5)
-> Materialize (cost=1.25..1.25 rows=5)
-> Limit: 5 row(s) (cost=0.75 rows=5)
-> Sort: t1.j, limit input to 5 row(s) per chunk (cost=0.75 rows=5)
-> Table scan on t1 (cost=0.75 rows=5)
DROP TABLE t1;
|
