Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11
-
None
-
None
Description
Hi, in the following test case, I create a table t2 with 100000 random values. Then there are two equivalent queries: one is a normal SELECT, and another is a prepared SELECT. However, I found the normal one is slower than the prepared one (the time of PREPARE plus the time of EXECUTE), which is unexpected. I printed the query plan for them and found that the normal SELECT has an unnecessary `where` in the subquery. In general, the query plan generated for prepared statements is not optimal, so I believe there is still room to further optimize the query plan of normal queries.
CREATE TABLE t2(c0 BOOLEAN); |
INSERT INTO t2 (c0) SELECT RAND() FROM seq_1_to_100000; |
SET PROFILING = 1; |
SELECT subq_0.subq_0_c0, t2.c0 FROM t2 STRAIGHT_JOIN (SELECT t2.c0 AS subq_0_c0 FROM t2 GROUP BY t2.c0) AS subq_0 ON ((CAST(-689309260 AS DECIMAL) | subq_0.subq_0_c0) << subq_0.subq_0_c0); |
SET @a = -689309260; |
PREPARE prepare_query FROM 'SELECT subq_0.subq_0_c0, t2.c0 FROM t2 STRAIGHT_JOIN (SELECT t2.c0 AS subq_0_c0 FROM t2 GROUP BY t2.c0) AS subq_0 ON ((CAST(? AS DECIMAL) | subq_0.subq_0_c0) << subq_0.subq_0_c0)'; |
EXECUTE prepare_query USING @a; |
DEALLOCATE PREPARE prepare_query; |
SHOW PROFILES;
|
SET PROFILING = 0; |
EXPLAIN SELECT subq_0.subq_0_c0, t2.c0 FROM t2 STRAIGHT_JOIN (SELECT t2.c0 AS subq_0_c0 FROM t2 GROUP BY t2.c0) AS subq_0 ON ((CAST(-689309260 AS DECIMAL) | subq_0.subq_0_c0) << subq_0.subq_0_c0); |
SET @a = -689309260; |
PREPARE prepare_query FROM 'EXPLAIN SELECT subq_0.subq_0_c0, t2.c0 FROM t2 STRAIGHT_JOIN (SELECT t2.c0 AS subq_0_c0 FROM t2 GROUP BY t2.c0) AS subq_0 ON ((CAST(? AS DECIMAL) | subq_0.subq_0_c0) << subq_0.subq_0_c0)'; |
EXECUTE prepare_query USING @a; |
DEALLOCATE PREPARE prepare_query; |
This is the output:
Query_ID Duration Query
|
1 0.04080790 SELECT subq_0.subq_0_c0, t2.c0 FROM t2 STRAIGHT_JOIN (SELECT t2.c0 AS subq_0_c0 FROM t2 GROUP BY t2.c0) AS subq_0 ON ((CAST(-689309260 AS DECIMAL) | subq_0.subq_0_c0) << subq_0.subq_0_c0)
|
2 0.00006023 SET @a = -689309260
|
3 0.00012934 PREPARE prepare_query FROM 'SELECT subq_0.subq_0_c0, t2.c0 FROM t2 STRAIGHT_JOIN (SELECT t2.c0 AS subq_0_c0 FROM t2 GROUP BY t2.c0) AS subq_0 ON ((CAST(? AS DECIMAL) | subq_0.subq_0_c0) << subq_0.subq_0_c0)'
|
4 0.03540023 EXECUTE prepare_query USING @a
|
5 0.00005005 DEALLOCATE PREPARE prepare_query
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 100000
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 100000 Using where; Using join buffer (flat, BNL join)
|
2 DERIVED t2 ALL NULL NULL NULL NULL 100000 Using where; Using temporary; Using filesort
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 100000
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 100000 Using where; Using join buffer (flat, BNL join)
|
2 DERIVED t2 ALL NULL NULL NULL NULL 100000 Using temporary; Using filesort
|