diff options
| author | Sergei Petrunia <psergey@askmonty.org> | 2019-01-23 15:49:49 +0300 |
|---|---|---|
| committer | Sergei Petrunia <psergey@askmonty.org> | 2019-01-23 15:52:21 +0300 |
| commit | b7a784ae259c0fd8ed1adc88b84dcdaa2441987c (patch) | |
| tree | 2e21a1c297d443e32c57ea1923909377610fd58b /mysql-test/main/range_vs_index_merge.result | |
| parent | 36a2a185fe18d31a644da46cfabd9757a379280c (diff) | |
| download | mariadb-git-b7a784ae259c0fd8ed1adc88b84dcdaa2441987c.tar.gz | |
MDEV-17761: Odd optimizer choice with ORDER BY LIMIT and condition selectivity
Make the "ORDER BY ... LIMIT n" optimizer take into account condition
selectivity data from EITS (not just from potential range accesses).
Diffstat (limited to 'mysql-test/main/range_vs_index_merge.result')
| -rw-r--r-- | mysql-test/main/range_vs_index_merge.result | 32 |
1 files changed, 32 insertions, 0 deletions
diff --git a/mysql-test/main/range_vs_index_merge.result b/mysql-test/main/range_vs_index_merge.result index b10e499aad8..59c093ff010 100644 --- a/mysql-test/main/range_vs_index_merge.result +++ b/mysql-test/main/range_vs_index_merge.result @@ -1320,6 +1320,37 @@ WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) AND (Population >= 100000 AND Population < 120000) ORDER BY Population LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE City index_merge Country,Name,Population Name,Country 35,3 NULL # Using sort_union(Name,Country); Using where; Using filesort +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; +ID Name Country Population +519 Worthing GBR 100000 +638 al-Arish EGY 100447 +518 Basildon GBR 100924 +707 Marbella ESP 101144 +3792 Tartu EST 101246 +SHOW STATUS LIKE 'Handler_read_%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 2 +Handler_read_last 0 +Handler_read_next 385 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 377 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +set @tmp_mdev585=@@optimizer_use_condition_selectivity; +set optimizer_use_condition_selectivity=1; +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; +id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City range Country,Name,Population Population 4 NULL # Using where FLUSH STATUS; SELECT * FROM City @@ -1343,6 +1374,7 @@ Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 +set optimizer_use_condition_selectivity=@tmp_mdev585; set optimizer_switch='index_merge=off'; EXPLAIN SELECT * FROM City |