Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.1.2
-
None
-
None
Description
Hi,
in the following test case, I create a table t2 with 1000 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 executes the subquery twice, but the prepared SELECT executes the subquery once. 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 OR REPLACE TABLE t2(c0 REAL ); |
INSERT INTO t2 (c0) SELECT RAND() * 100 FROM seq_1_to_1000; |
SET PROFILING = 1; |
SELECT LOCATE(CAST(1135187220 AS DECIMAL), (t2.c0 IN ((SELECT CAST(-2049139158 AS DECIMAL) FROM t2 GROUP BY CAST(-2049139158 AS DECIMAL))))) FROM t2 GROUP BY LOCATE(CAST(1135187220 AS DECIMAL), (t2.c0 IN ((SELECT CAST(-2049139158 AS DECIMAL) FROM t2 GROUP BY CAST(-2049139158 AS DECIMAL))))); |
SET @a = 1135187220; -- 0ms; |
SET @b = -2049139158; -- 0ms; |
SET @c = -2049139158; -- 0ms; |
SET @d = 1135187220; -- 1ms; |
SET @e = -2049139158; -- 0ms; |
SET @f = -2049139158; -- 0ms; |
PREPARE prepare_query FROM 'SELECT LOCATE(CAST(? AS DECIMAL), (t2.c0 IN ((SELECT CAST(? AS DECIMAL) FROM t2 GROUP BY CAST(? AS DECIMAL))))) FROM t2 GROUP BY LOCATE(CAST(? AS DECIMAL), (t2.c0 IN ((SELECT CAST(? AS DECIMAL) FROM t2 GROUP BY CAST(? AS DECIMAL)))))'; |
EXECUTE prepare_query USING @a,@b,@c,@d,@e,@f; |
SET PROFILING = 0; |
SHOW PROFILES;
|
ANALYZE SELECT LOCATE(CAST(1135187220 AS DECIMAL), (t2.c0 IN ((SELECT CAST(-2049139158 AS DECIMAL) FROM t2 GROUP BY CAST(-2049139158 AS DECIMAL))))) FROM t2 GROUP BY LOCATE(CAST(1135187220 AS DECIMAL), (t2.c0 IN ((SELECT CAST(-2049139158 AS DECIMAL) FROM t2 GROUP BY CAST(-2049139158 AS DECIMAL))))); |
PREPARE prepare_query FROM 'ANALYZE SELECT LOCATE(CAST(? AS DECIMAL), (t2.c0 IN ((SELECT CAST(? AS DECIMAL) FROM t2 GROUP BY CAST(? AS DECIMAL))))) FROM t2 GROUP BY LOCATE(CAST(? AS DECIMAL), (t2.c0 IN ((SELECT CAST(? AS DECIMAL) FROM t2 GROUP BY CAST(? AS DECIMAL)))))'; |
EXECUTE prepare_query USING @a,@b,@c,@d,@e,@f; |
This is the outputs:
Query_ID Duration Query
|
1 0.00426944 SELECT LOCATE(CAST(1135187220 AS DECIMAL), (t2.c0 IN ((SELECT CAST(-2049139158 AS DECIMAL) FROM t2 GROUP BY CAST(-2049139158 AS DECIMAL))))) FROM t2 GROUP BY LOCATE(CAST(1135187220 AS DECIMAL), (t2.c0 IN ((SELECT CAST(-2049139158 AS DECIMAL) FROM t2 GROUP BY CAST(-2049139158 AS DECIMAL))))) |
2 0.00002044 SET @a = 1135187220 |
3 0.00001313 SET @b = -2049139158 |
4 0.00001194 SET @c = -2049139158 |
5 0.00001189 SET @d = 1135187220 |
6 0.00001118 SET @e = -2049139158 |
7 0.00001143 SET @f = -2049139158 |
8 0.00006423 PREPARE prepare_query FROM 'SELECT LOCATE(CAST(? AS DECIMAL), (t2.c0 IN ((SELECT CAST(? AS DECIMAL) FROM t2 GROUP BY CAST(? AS DECIMAL))))) FROM t2 GROUP BY LOCATE(CAST(? AS DECIMAL), (t2.c0 IN ((SELECT CAST(? AS DECIMAL) FROM t2 GROUP BY CAST(? AS DECIMAL)))))' |
9 0.00221809 EXECUTE prepare_query USING @a,@b,@c,@d,@e,@f |
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra |
1 PRIMARY t2 ALL NULL NULL NULL NULL 1000 1000.00 100.00 100.00 Using temporary; Using filesort |
3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1000 NULL 100.00 NULL |
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1000 NULL 100.00 NULL |
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra |
1 PRIMARY t2 ALL NULL NULL NULL NULL 1000 1000.00 100.00 100.00 Using temporary; Using filesort |
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1000 NULL 100.00 NULL |