Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
-
None
Description
Hi, in the following test case, I create two tables t0 and t1, with t0 being an empty table. Then there are two equivalent queries, one is a normal SELECT, and the other is a prepared SELECT. There are cross joins in the query and one table is t0. When I executed these two queries I found the prepared SELECT (including PREPARE and EXECUTE) faster than the normal query. Then I found that the normal query generated an ineffective query plan. 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 t0(c0 CHAR(100) PRIMARY KEY, c1 CHAR(100) NOT NULL) engine=MyISAM; |
CREATE OR REPLACE TABLE t1 LIKE t0; |
INSERT INTO t1 (c0, c1) SELECT UUID(), UUID() FROM seq_1_to_10000; |
TRUNCATE t0 NOWAIT; |
SET PROFILING = 1; |
SELECT t0.c1, t0.c0, t1.c1, t1.c0 FROM t0 CROSS JOIN t1 ON TRIM((' I' NOT IN (CAST(-859055906 AS DECIMAL)))) WHERE (((true IS TRUE) NOT IN (CAST(-747246192 AS DECIMAL), '3gd,C&', 'futooow!')) XOR ((t0.c0 IN (t0.c1, '', t1.c0)) IS NULL)); |
SET @a = ' I'; |
SET @b = -859055906; |
SET @c = -747246192; |
SET @d = '3gd,C&'; |
SET @e = 'futooow!'; |
PREPARE prepare_query FROM 'SELECT t0.c1, t0.c0, t1.c1, t1.c0 FROM t0 CROSS JOIN t1 ON TRIM((? NOT IN (CAST(? AS DECIMAL)))) WHERE (((true IS TRUE) NOT IN (CAST(? AS DECIMAL), ?, ?)) XOR ((t0.c0 IN (t0.c1, ?, t1.c0)) IS NULL))'; |
EXECUTE prepare_query USING @a,@b,@c,@d,@e,@f; |
DEALLOCATE PREPARE prepare_query; |
SHOW PROFILES;
|
SET PROFILING = 0; |
EXPLAIN SELECT t0.c1, t0.c0, t1.c1, t1.c0 FROM t0 CROSS JOIN t1 ON TRIM((' I' NOT IN (CAST(-859055906 AS DECIMAL)))) WHERE (((true IS TRUE) NOT IN (CAST(-747246192 AS DECIMAL), '3gd,C&', 'futooow!')) XOR ((t0.c0 IN (t0.c1, '', t1.c0)) IS NULL)); |
SET @a = ' I'; |
SET @b = -859055906; |
SET @c = -747246192; |
SET @d = '3gd,C&'; |
SET @e = 'futooow!'; |
PREPARE prepare_query FROM 'EXPLAIN SELECT t0.c1, t0.c0, t1.c1, t1.c0 FROM t0 CROSS JOIN t1 ON TRIM((? NOT IN (CAST(? AS DECIMAL)))) WHERE (((true IS TRUE) NOT IN (CAST(? AS DECIMAL), ?, ?)) XOR ((t0.c0 IN (t0.c1, ?, t1.c0)) IS NULL))'; |
EXECUTE prepare_query USING @a,@b,@c,@d,@e,@f; |
DEALLOCATE PREPARE prepare_query; |
This is the output:
Query_ID Duration Query
|
1 0.00067938 SELECT t0.c1, t0.c0, t1.c1, t1.c0 FROM t0 CROSS JOIN t1 ON TRIM((' I' NOT IN (CAST(-859055906 AS DECIMAL)))) WHERE (((true IS TRUE) NOT IN (CAST(-747246192 AS DECIMAL), '3gd,C&', 'futooow!')) XOR ((t0.c0 IN (t0.c1, '', t1.c0)) IS NULL)) |
2 0.00002311 SET @a = ' I' |
3 0.00001588 SET @b = -859055906 |
4 0.00001541 SET @c = -747246192 |
5 0.00001557 SET @d = '3gd,C&' |
6 0.00001483 SET @e = 'futooow!' |
7 0.00005231 PREPARE prepare_query FROM 'SELECT t0.c1, t0.c0, t1.c1, t1.c0 FROM t0 CROSS JOIN t1 ON TRIM((? NOT IN (CAST(? AS DECIMAL)))) WHERE (((true IS TRUE) NOT IN (CAST(? AS DECIMAL), ?, ?)) XOR ((t0.c0 IN (t0.c1, ?, t1.c0)) IS NULL))' |
8 0.00006714 EXECUTE prepare_query USING @a,@b,@c,@d,@e,@f |
9 0.00001590 DEALLOCATE PREPARE prepare_query |
id select_type table type possible_keys key key_len ref rows Extra |
1 SIMPLE t0 system NULL NULL NULL NULL 0 Const row not found |
1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 |
id select_type table type possible_keys key key_len ref rows Extra |
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables |