204 lines
12 KiB
Plaintext
204 lines
12 KiB
Plaintext
"WL6369 Explain for Connection"
|
|
QUERY: DELETE from t1
|
|
STATUS:NOT_EQUAL
|
|
SYNC_POINT:before_reset_query_plan
|
|
Diff: EXPLAIN NORMAL vs OTHER
|
|
@@ -1,2 +1,2 @@
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
-1 DELETE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Deleting all rows
|
|
+1 DELETE NULL NULL NULL NULL NULL NULL NULL NULL NULL Plan isn't ready yet
|
|
QUERY: SELECT * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b
|
|
STATUS: EQUAL
|
|
QUERY: SELECT 1 FROM ( SELECT 1, 2 FROM DUAL WHERE EXISTS ( SELECT f1 FROM t1 )) AS tt
|
|
STATUS:NOT_EQUAL
|
|
SYNC_POINT:after_join_optimize
|
|
Diff: EXPLAIN NORMAL vs OTHER
|
|
@@ -1,4 +0,0 @@
|
|
-id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table
|
|
-2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
-3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table
|
|
QUERY: SELECT STRAIGHT_JOIN * FROM t3 JOIN t1 ON t3.a=t1.a JOIN t2 ON t3.a=t2.a JOIN t4 WHERE t4.a IN (t1.b, t2.b)
|
|
STATUS:NOT_EQUAL
|
|
SYNC_POINT:before_reset_query_plan
|
|
Diff: EXPLAIN NORMAL vs OTHER
|
|
@@ -4,2 +4,2 @@
|
|
1 SIMPLE t2 NULL eq_ref PRIMARY PRIMARY 4 test.t3.a 1 100.00 NULL
|
|
-1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 7 28.57 Range checked for each record (index map: 0x1)
|
|
+1 SIMPLE t4 NULL range PRIMARY PRIMARY 4 NULL 7 100.00 Range checked for each record (index map: 0x1)
|
|
QUERY: SELECT STRAIGHT_JOIN (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) FROM t3, t1, t2 WHERE t3.a=t1.a AND t3.a=t2.a
|
|
STATUS: EQUAL
|
|
QUERY: SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b
|
|
STATUS: EQUAL
|
|
QUERY: DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000
|
|
STATUS:NOT_EQUAL
|
|
SYNC_POINT:planned_single_delete
|
|
Diff: EXPLAIN NORMAL vs OTHER
|
|
@@ -1,3 +0,0 @@
|
|
-id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
-1 DELETE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
-2 SUBQUERY t1 NULL range a a 5 NULL 11 100.00 Using index for group-by
|
|
QUERY: SELECT * FROM t1 WHERE key1=1130 AND col1 IS NULL ORDER BY text1
|
|
STATUS:NOT_EQUAL
|
|
SYNC_POINT:before_reset_query_plan
|
|
Diff: EXPLAIN NORMAL vs OTHER
|
|
@@ -1,2 +1,2 @@
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
-1 SIMPLE t1 NULL ref key1 key1 4 const 4 20.00 Using where; Using filesort
|
|
+1 SIMPLE t1 NULL ref key1 key1 4 const 4 100.00 Using where; Using filesort
|
|
QUERY: SELECT t1.c2 FROM t2 STRAIGHT_JOIN t1 ON t1.c1 < t2.c1
|
|
STATUS: EQUAL
|
|
QUERY: SELECT STRAIGHT_JOIN * FROM (t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key) ON t2.c1 = t4.c3) ON t1.c1 = t4.c2) RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1 WHERE t1.i1 = 1
|
|
STATUS: EQUAL
|
|
QUERY: SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL GROUP BY t2.f1, t2.f2
|
|
STATUS:NOT_EQUAL
|
|
SYNC_POINT:before_reset_query_plan
|
|
Diff: EXPLAIN NORMAL vs OTHER
|
|
@@ -2,2 +2,2 @@
|
|
1 SIMPLE t1 NULL system PRIMARY NULL NULL NULL 1 100.00 NULL
|
|
-1 SIMPLE t2 NULL ref PRIMARY PRIMARY 4 const 1 50.00 Using where; Using index
|
|
+1 SIMPLE t2 NULL ref PRIMARY PRIMARY 4 const 1 100.00 Using where; Using index
|
|
QUERY: SELECT * FROM t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref
|
|
STATUS:NOT_EQUAL
|
|
SYNC_POINT:before_reset_query_plan
|
|
Diff: EXPLAIN NORMAL vs OTHER
|
|
@@ -2,2 +2,2 @@
|
|
1 SIMPLE a NULL ALL id NULL NULL NULL 10 100.00 NULL
|
|
-1 SIMPLE b NULL ref id id 4 test.a.id 1 33.33 Using where
|
|
+1 SIMPLE b NULL ref id id 4 test.a.id 1 100.00 Using where
|
|
QUERY: DELETE FROM a USING t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref
|
|
STATUS:NOT_EQUAL
|
|
SYNC_POINT:before_reset_query_plan
|
|
Diff: EXPLAIN NORMAL vs OTHER
|
|
@@ -2,2 +2,2 @@
|
|
1 DELETE a NULL ALL id NULL NULL NULL 10 100.00 NULL
|
|
-1 SIMPLE b NULL ref id id 4 test.a.id 1 33.33 Using where
|
|
+1 SIMPLE b NULL ref id id 4 test.a.id 1 100.00 Using where
|
|
QUERY: SELECT * from t2 left outer join t1 using (n)
|
|
STATUS: EQUAL
|
|
QUERY: DELETE t1,t2 from t2 left outer join t1 using (n)
|
|
STATUS: EQUAL
|
|
QUERY: UPDATE t1 left join t2 on t1.a=t2.a set t1.b=2, t2.b=2 where t1.b=1 and t2.b=1 or t2.a is NULL
|
|
STATUS: EQUAL
|
|
QUERY: UPDATE t1 LEFT JOIN t2 USING(id) SET s1 = 'b'
|
|
STATUS: EQUAL
|
|
QUERY: SELECT * from t1 where a = 1 and b is null order by a desc, b desc
|
|
STATUS: EQUAL
|
|
QUERY: SELECT * from t1 where b=1 or b is null order by a
|
|
STATUS: EQUAL
|
|
QUERY: SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c
|
|
STATUS:NOT_EQUAL
|
|
SYNC_POINT:before_reset_query_plan
|
|
Diff: EXPLAIN NORMAL vs OTHER
|
|
@@ -1,2 +1,2 @@
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
-1 SIMPLE t1 NULL ref a_c,a a 5 const 1 75.00 Using where; Using filesort
|
|
+1 SIMPLE t1 NULL ref a_c,a a 5 const 1 100.00 Using where; Using filesort
|
|
QUERY: SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC
|
|
STATUS:NOT_EQUAL
|
|
SYNC_POINT:before_reset_query_plan
|
|
Diff: EXPLAIN NORMAL vs OTHER
|
|
@@ -1,2 +1,2 @@
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
-1 SIMPLE t1 NULL ref a_c,a a 5 const 1 75.00 Using where; Using filesort
|
|
+1 SIMPLE t1 NULL ref a_c,a a 5 const 1 100.00 Using where; Using filesort
|
|
QUERY: UPDATE t1 SET b = CONCAT(b, ', max(a) in t2: ', (SELECT MAX(a) FROM t2)) WHERE a = 5
|
|
STATUS:NOT_EQUAL
|
|
SYNC_POINT:planned_single_update
|
|
Diff: EXPLAIN NORMAL vs OTHER
|
|
@@ -2,2 +2,2 @@
|
|
1 UPDATE t1 p5 range PRIMARY PRIMARY 4 const 1 100.00 Using where
|
|
-2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
|
|
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Plan isn't ready yet
|
|
QUERY: UPDATE t1, t2 SET t1.b = CONCAT(t1.b, ', t2.b:', t2.b), t2.b = CONCAT(t2.b, ', t1.b:', t1.b) WHERE t2.b = t1.b and t2.a = 4
|
|
STATUS: EQUAL
|
|
QUERY: SELECT s.oxid FROM t1 v, t1 s WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright
|
|
STATUS:NOT_EQUAL
|
|
SYNC_POINT:before_reset_query_plan
|
|
Diff: EXPLAIN NORMAL vs OTHER
|
|
@@ -2,2 +2,2 @@
|
|
1 SIMPLE v NULL ref OXLEFT,OXRIGHT,OXROOTID OXROOTID 34 const 5 100.00 NULL
|
|
-1 SIMPLE s NULL ref OXLEFT,OXROOTID OXROOTID 34 const 5 16.67 Using where
|
|
+1 SIMPLE s NULL ref OXLEFT,OXROOTID OXROOTID 34 const 5 100.00 Using where
|
|
QUERY: SELECT * from t1 where b = (select b from t2 where t1.a = t2.a)
|
|
STATUS: EQUAL
|
|
QUERY: DELETE from t1 where b = (select b from t2 where t1.a = t2.a)
|
|
STATUS:NOT_EQUAL
|
|
SYNC_POINT:planned_single_delete
|
|
Diff: EXPLAIN NORMAL vs OTHER
|
|
@@ -2,2 +2,2 @@
|
|
1 DELETE t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
-2 DEPENDENT SUBQUERY t2 NULL eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 NULL
|
|
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Plan isn't ready yet
|
|
QUERY: SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE numeropost=topic)
|
|
STATUS: EQUAL
|
|
QUERY: DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE numeropost=topic))
|
|
STATUS:NOT_EQUAL
|
|
SYNC_POINT:planned_single_delete
|
|
Diff: EXPLAIN NORMAL vs OTHER
|
|
@@ -2,3 +2,2 @@
|
|
1 DELETE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
-2 DEPENDENT SUBQUERY t2 NULL ref topic topic 3 func 2 100.00 Using index
|
|
-2 DEPENDENT SUBQUERY t3 NULL eq_ref PRIMARY PRIMARY 3 test.t2.topic 1 100.00 Using where; Not exists; Using index
|
|
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Plan isn't ready yet
|
|
QUERY: SELECT cns.id, cns.max_anno_dep, cns.max_anno_dep = (SELECT s.anno_dep FROM t1 AS s WHERE s.id_cns = cns.id ORDER BY s.anno_dep DESC LIMIT 1) AS PIPPO FROM t2 AS cns
|
|
STATUS: EQUAL
|
|
QUERY: SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.a ORDER BY t1.b LIMIT 1) AS d1 FROM t2
|
|
STATUS: EQUAL
|
|
QUERY: SELECT t1.pk FROM t1 WHERE t1.col_varchar_key < ALL ( SELECT * FROM v1 )
|
|
STATUS:NOT_EQUAL
|
|
SYNC_POINT:before_reset_query_plan
|
|
Diff: EXPLAIN NORMAL vs OTHER
|
|
@@ -2,5 +2,5 @@
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table
|
|
-2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Not optimized, outer query is empty
|
|
-3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL Not optimized, outer query is empty
|
|
-4 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL Not optimized, outer query is empty
|
|
-5 UNION RESULT NULL NULL NULL NULL NULL NULL NULL NULL NULL Not optimized, outer query is empty
|
|
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Plan isn't ready yet
|
|
+3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL Plan isn't ready yet
|
|
+4 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL Plan isn't ready yet
|
|
+5 UNION RESULT NULL NULL NULL NULL NULL NULL NULL NULL NULL Plan isn't ready yet
|
|
QUERY: SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a
|
|
STATUS: EQUAL
|
|
QUERY: SELECT alias2.col_varchar_nokey FROM v1 AS alias1 RIGHT JOIN t1 AS alias2 ON 1 WHERE alias2.col_varchar_key IN ( SELECT sq2_alias1.col_varchar_nokey FROM v1 AS sq2_alias1 LEFT JOIN t1 AS sq2_alias2 ON (sq2_alias2.col_int_key = sq2_alias1.pk) WHERE sq2_alias1.pk != alias1.col_int_key AND sq2_alias1.col_varchar_key > alias1.col_varchar_key )
|
|
STATUS:NOT_EQUAL
|
|
SYNC_POINT:after_join_optimize
|
|
Diff: EXPLAIN NORMAL vs OTHER
|
|
@@ -3,3 +3,3 @@
|
|
1 SIMPLE alias2 NULL ALL col_varchar_key NULL NULL NULL 5 20.00 Using where; Using join buffer (Block Nested Loop)
|
|
-1 SIMPLE t1 NULL ALL col_varchar_key NULL NULL NULL 5 26.66 Range checked for each record (index map: 0x4)
|
|
+1 SIMPLE t1 NULL ALL col_varchar_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x4)
|
|
1 SIMPLE sq2_alias2 NULL ref col_int_key col_int_key 4 func 2 100.00 Using where; Using index; End temporary
|
|
QUERY: SELECT alias2.col_varchar_nokey FROM t1 AS alias2 LEFT JOIN v1 AS alias1 ON 1 WHERE alias2.col_varchar_key IN ( SELECT sq2_alias1.col_varchar_nokey FROM v1 AS sq2_alias1 LEFT JOIN t1 AS sq2_alias2 ON (sq2_alias2.col_int_key = sq2_alias1.pk) WHERE sq2_alias1.pk != alias1.col_int_key AND sq2_alias1.col_varchar_key > alias1.col_varchar_key )
|
|
STATUS:NOT_EQUAL
|
|
SYNC_POINT:after_join_optimize
|
|
Diff: EXPLAIN NORMAL vs OTHER
|
|
@@ -3,3 +3,3 @@
|
|
1 SIMPLE alias2 NULL ALL col_varchar_key NULL NULL NULL 5 20.00 Using where; Using join buffer (Block Nested Loop)
|
|
-1 SIMPLE t1 NULL ALL col_varchar_key NULL NULL NULL 5 26.66 Range checked for each record (index map: 0x4)
|
|
+1 SIMPLE t1 NULL ALL col_varchar_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x4)
|
|
1 SIMPLE sq2_alias2 NULL ref col_int_key col_int_key 4 func 2 100.00 Using where; Using index; End temporary
|
|
QUERY: SELECT d FROM (SELECT * FROM t2) AS a2 RIGHT JOIN (SELECT * FROM t1) AS a1 ON c = a WHERE d LIKE '_' ORDER BY d
|
|
STATUS: EQUAL
|
|
QUERY: SELECT (SELECT rs.t2_id FROM t2 rs WHERE rs.t1_id= (SELECT lt.t1_id FROM t1 lt WHERE lt.t3_id=a.t3_id) ORDER BY b DESC LIMIT 1) from t3 AS a
|
|
STATUS: EQUAL
|
|
QUERY: UPDATE t1 join t2 on (t1.a=t2.a) set t1.id=t2.id
|
|
STATUS: EQUAL
|
|
QUERY: SELECT DISTINCT alias2.col_int AS field1 , alias2.col_int AS field2 FROM a1 AS alias1 LEFT JOIN aa1 AS alias2 ON alias1.pk = alias2.pk WHERE ( alias1.col_int = 8 OR alias1 .pk <= alias1.col_int ) GROUP BY field1, field2 ORDER BY field1, field2 DESC
|
|
STATUS: EQUAL
|
|
QUERY: SELECT DISTINCT alias1.pk AS field1 FROM f1 AS alias1 LEFT JOIN a2 AS alias2 ON alias1.col_varchar_1024_latin1_key = alias2 .col_varchar_10_utf8_key WHERE alias1.pk IN (5, 5) GROUP BY field1 HAVING field1 <= 3
|
|
STATUS: EQUAL
|
|
QUERY: SELECT alias1.pk AS field1 FROM h2 AS alias1 LEFT JOIN aa3 AS alias2 ON alias1.pk = alias2.col_int_key WHERE alias1.pk <> 9 GROUP BY field1 ORDER BY field1 LIMIT 1 OFFSET 3
|
|
STATUS: EQUAL
|
|
QUERY: SELECT alias1.pk AS field1 FROM e1 AS alias1 RIGHT JOIN cc2 AS alias2 ON alias1.col_int = alias2.col_int WHERE alias1.col_int_key IN (8)
|
|
STATUS:NOT_EQUAL
|
|
SYNC_POINT:before_reset_query_plan
|
|
Diff: EXPLAIN NORMAL vs OTHER
|
|
@@ -2,2 +2,2 @@
|
|
1 SIMPLE alias1 NULL ref col_int_key col_int_key 5 const 1 100.00 NULL
|
|
-1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (Block Nested Loop)
|
|
+1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
|
|
QUERY: SELECT DISTINCT alias2.pk AS field1, alias2.pk AS field2, alias2.pk AS field3 FROM t1 AS alias1 RIGHT JOIN t2 AS alias2 ON alias1.col_varchar_key = alias2.col_varchar_key WHERE alias1.col_int >alias1.col_int HAVING field3 = 7 ORDER BY field1, field2, field3 LIMIT 10 OFFSET 1
|
|
STATUS: EQUAL
|