Description:
The execution performance of the default execution plan is poor. If ignore index is used, the query performance is great
How to repeat:
source tbl_3_all_typeindex_desc.sql
source tbl_1_all_type2index.sql
alter table tbl_3_all_typeindex_desc add index `idx_tb_smallint_c_cs_1` (`smallint_col`);
WITH cte1 AS (SELECT tbl_3_all_typeindex_desc.* FROM tbl_3_all_typeindex_desc ignore index(idx_tb_smallint_c_cs_1) join tbl_1_all_type2index ON tbl_3_all_typeindex_desc.smallint_col=tbl_1_all_type2index.smallint_col WHERE tbl_3_all_typeindex_desc.smallint_col NOT LIKE '1%' ) SELECT set_col FROM (WITH cte2 AS (SELECT c1.set_col,dt1.binary_col FROM cte1 AS c1 right join ( SELECT set_col,binary_col FROM tbl_1_all_type2index WHERE tbl_1_all_type2index.tinyint_col IN (0,108,110,126,127,-128,16,25,38,42,50,61,74,87,98,NULL) ORDER BY 1,2 LIMIT 1001) AS dt1 ON c1.set_col=dt1.set_col ) SELECT * FROM cte2) AS dt2 ORDER BY 1 limit 5;
WITH cte1 AS (SELECT tbl_3_all_typeindex_desc.* FROM tbl_3_all_typeindex_desc join tbl_1_all_type2index ON tbl_3_all_typeindex_desc.smallint_col=tbl_1_all_type2index.smallint_col WHERE tbl_3_all_typeindex_desc.smallint_col NOT LIKE '1%' ) SELECT set_col FROM (WITH cte2 AS (SELECT c1.set_col,dt1.binary_col FROM cte1 AS c1 right join ( SELECT set_col,binary_col FROM tbl_1_all_type2index WHERE tbl_1_all_type2index.tinyint_col IN (0,108,110,126,127,-128,16,25,38,42,50,61,74,87,98,NULL) ORDER BY 1,2 LIMIT 1001) AS dt1 ON c1.set_col=dt1.set_col ) SELECT * FROM cte2) AS dt2 ORDER BY 1 limit 5;
Suggested fix:
By default, the execution plan with the optimal performance is selected to improve query performance.
