diff options
Diffstat (limited to 'mysql-test/main/range_vs_index_merge.test')
| -rw-r--r-- | mysql-test/main/range_vs_index_merge.test | 29 |
1 files changed, 27 insertions, 2 deletions
diff --git a/mysql-test/main/range_vs_index_merge.test b/mysql-test/main/range_vs_index_merge.test index 84b87579e85..2c43027d757 100644 --- a/mysql-test/main/range_vs_index_merge.test +++ b/mysql-test/main/range_vs_index_merge.test @@ -229,13 +229,13 @@ EXPLAIN SELECT * FROM City WHERE ((ID < 800) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 900 AND 1500) AND - (Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000))); + (Name LIKE 'Pa%' OR (Population > 103000 AND Population < 105000))); EXPLAIN SELECT * FROM City WHERE ((ID < 200) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 100 AND 200) AND - (Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000))); + (Name LIKE 'Pa%' OR (Population > 103200 AND Population < 104000))); # The following 6 queries check that the plans @@ -718,6 +718,23 @@ SELECT * FROM City ORDER BY Population LIMIT 5; SHOW STATUS LIKE 'Handler_read_%'; +set @tmp_mdev585=@@optimizer_use_condition_selectivity; +set optimizer_use_condition_selectivity=1; +--replace_column 9 # +EXPLAIN +SELECT * FROM City + WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) + AND (Population >= 100000 AND Population < 120000) +ORDER BY Population LIMIT 5; + +FLUSH STATUS; +SELECT * FROM City + WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) + AND (Population >= 100000 AND Population < 120000) +ORDER BY Population LIMIT 5; +SHOW STATUS LIKE 'Handler_read_%'; + +set optimizer_use_condition_selectivity=@tmp_mdev585; set optimizer_switch='index_merge=off'; @@ -1069,6 +1086,8 @@ CREATE TABLE t1 ( INSERT INTO t1 VALUES (9,0,2,6), (9930,0,0,NULL); +analyze table t1; + SET SESSION optimizer_switch='index_merge_intersection=off'; SET SESSION optimizer_switch='index_merge_sort_union=off'; @@ -1101,6 +1120,8 @@ INSERT INTO t1 VALUES (97,0,7,6), (9937,0,7,7), (98,0,8,6), (9938,0,8,8), (99,0,9,6), (9939,0,9,9); +analyze table t1; + SET SESSION optimizer_switch='index_merge_union=off'; EXPLAIN @@ -1158,6 +1179,8 @@ INSERT INTO t1 VALUES (0,58,7,7),(0,63,2,0),(0,64,186,8),(0,65,1,-2), (0,71,190,-3), (0,72,321,-7),(0,73,0,3),(0,74,5,25),(0,75,5,3); +ANALYZE TABLE t1; + SET SESSION optimizer_switch='index_merge_sort_union=off'; EXPLAIN SELECT * FROM t1 @@ -1230,6 +1253,8 @@ INSERT INTO t1 VALUES (7,'Pennsylvania','Harrisburg'), (8,'Virginia','Richmond') ; + +ANALYZE TABLE t1; EXPLAIN SELECT * FROM t1 FORCE KEY (state,capital) |