Re: [Maria-developers] [Commits] Rev 3589: Fixed bug mdev-585 (LP bug #637962) in file:///home/igor/maria/maria-5.3-mdev585/
Hello Igor, The fix seems to be ok to push. Which version do you want to push it into? This is not a crash, should we really push it into 5.3, or to 5.5? On Thu, Nov 01, 2012 at 02:54:34PM -0700, Igor Babaev wrote:
At file:///home/igor/maria/maria-5.3-mdev585/
------------------------------------------------------------ revno: 3589 revision-id: igor@askmonty.org-20121101215433-ctfm3zfhc6uhuy3i parent: psergey@askmonty.org-20121031053425-kfn0l7yj6z7jtnqw committer: Igor Babaev <igor@askmonty.org> branch nick: maria-5.3-mdev585 timestamp: Thu 2012-11-01 14:54:33 -0700 message: Fixed bug mdev-585 (LP bug #637962) If, when executing a query with ORDER BY col LIMIT n, the optimizer chose an index-merge scan to access the table containing col while there existed an index defined over col then optimizer did not consider the possibility of using an alternative range scan by this index to avoid filesort. This could cause a performance degradation if the optimizer flag index_merge was set up to 'on'.
=== modified file 'mysql-test/r/range_vs_index_merge.result' --- a/mysql-test/r/range_vs_index_merge.result 2012-03-21 00:03:28 +0000 +++ b/mysql-test/r/range_vs_index_merge.result 2012-11-01 21:54:33 +0000 @@ -1221,6 +1221,150 @@ Seattle USA 563374 Caracas VEN 1975294 set optimizer_switch=@save_optimizer_switch; +# +# Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n +# (LP bug #637962) +# +DROP INDEX CountryPopulation ON City; +DROP INDEX CountryName ON City; +DROP INDEX CityName on City; +CREATE INDEX Name ON City(Name); +CREATE INDEX Population ON City(Population); +EXPLAIN +SELECT * FROM City +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) +AND (Population >= 100000 AND Population < 120000); +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 +FLUSH STATUS; +SELECT * FROM City +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) +AND (Population >= 100000 AND Population < 120000); +ID Name Country Population +384 Cabo Frio BRA 119503 +387 Camaragibe BRA 118968 +403 Catanduva BRA 107761 +412 Cachoeirinha BRA 103240 +508 Watford GBR 113080 +509 Ipswich GBR 114000 +510 Slough GBR 112000 +511 Exeter GBR 111000 +512 Cheltenham GBR 106000 +513 Gloucester GBR 107000 +514 Saint Helens GBR 106293 +515 Sutton Coldfield GBR 106001 +516 York GBR 104425 +517 Oldham GBR 103931 +518 Basildon GBR 100924 +519 Worthing GBR 100000 +635 Mallawi EGY 119283 +636 Bilbays EGY 113608 +637 Mit Ghamr EGY 101801 +638 al-Arish EGY 100447 +701 Tarragona ESP 113016 +702 Lleida (Lérida) ESP 112207 +703 Jaén ESP 109247 +704 Ourense (Orense) ESP 109120 +705 Mataró ESP 104095 +706 Algeciras ESP 103106 +707 Marbella ESP 101144 +759 Gonder ETH 112249 +869 Cabuyao PHL 106630 +870 Calapan PHL 105910 +873 Cauayan PHL 103952 +903 Serekunda GMB 102600 +909 Sohumi GEO 111700 +913 Tema GHA 109975 +914 Sekondi-Takoradi GHA 103653 +924 Villa Nueva GTM 101295 +1844 Cape Breton CAN 114733 +1847 Cambridge CAN 109186 +2406 Herakleion GRC 116178 +2407 Kallithea GRC 114233 +2408 Larisa GRC 113090 +2908 Cajamarca PER 108009 +3002 Besançon FRA 117733 +3003 Caen FRA 113987 +3004 Orléans FRA 113126 +3005 Mulhouse FRA 110359 +3006 Rouen FRA 106592 +3007 Boulogne-Billancourt FRA 106367 +3008 Perpignan FRA 105115 +3009 Nancy FRA 103605 +3411 Ceyhan TUR 102412 +3567 Carúpano VEN 119639 +3568 Catia La Mar VEN 117012 +3571 Calabozo VEN 107146 +3786 Cam Ranh VNM 114041 +3792 Tartu EST 101246 +4002 Carrollton USA 109576 +4027 Cape Coral USA 102286 +4032 Cambridge USA 101355 +SHOW STATUS LIKE 'Handler_read_%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 2 +Handler_read_next 385 +Handler_read_prev 0 +Handler_read_rnd 377 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +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 +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 1 +Handler_read_next 59 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +set optimizer_switch='index_merge=off'; +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 index condition; Using where +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 1 +Handler_read_next 59 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +set optimizer_switch=@save_optimizer_switch; DROP DATABASE world; use test; CREATE TABLE t1 (
=== modified file 'mysql-test/r/range_vs_index_merge_innodb.result' --- a/mysql-test/r/range_vs_index_merge_innodb.result 2012-03-21 00:03:28 +0000 +++ b/mysql-test/r/range_vs_index_merge_innodb.result 2012-11-01 21:54:33 +0000 @@ -1222,6 +1222,150 @@ Seattle USA 563374 Caracas VEN 1975294 set optimizer_switch=@save_optimizer_switch; +# +# Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n +# (LP bug #637962) +# +DROP INDEX CountryPopulation ON City; +DROP INDEX CountryName ON City; +DROP INDEX CityName on City; +CREATE INDEX Name ON City(Name); +CREATE INDEX Population ON City(Population); +EXPLAIN +SELECT * FROM City +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) +AND (Population >= 100000 AND Population < 120000); +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 +FLUSH STATUS; +SELECT * FROM City +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) +AND (Population >= 100000 AND Population < 120000); +ID Name Country Population +384 Cabo Frio BRA 119503 +387 Camaragibe BRA 118968 +403 Catanduva BRA 107761 +412 Cachoeirinha BRA 103240 +508 Watford GBR 113080 +509 Ipswich GBR 114000 +510 Slough GBR 112000 +511 Exeter GBR 111000 +512 Cheltenham GBR 106000 +513 Gloucester GBR 107000 +514 Saint Helens GBR 106293 +515 Sutton Coldfield GBR 106001 +516 York GBR 104425 +517 Oldham GBR 103931 +518 Basildon GBR 100924 +519 Worthing GBR 100000 +635 Mallawi EGY 119283 +636 Bilbays EGY 113608 +637 Mit Ghamr EGY 101801 +638 al-Arish EGY 100447 +701 Tarragona ESP 113016 +702 Lleida (Lérida) ESP 112207 +703 Jaén ESP 109247 +704 Ourense (Orense) ESP 109120 +705 Mataró ESP 104095 +706 Algeciras ESP 103106 +707 Marbella ESP 101144 +759 Gonder ETH 112249 +869 Cabuyao PHL 106630 +870 Calapan PHL 105910 +873 Cauayan PHL 103952 +903 Serekunda GMB 102600 +909 Sohumi GEO 111700 +913 Tema GHA 109975 +914 Sekondi-Takoradi GHA 103653 +924 Villa Nueva GTM 101295 +1844 Cape Breton CAN 114733 +1847 Cambridge CAN 109186 +2406 Herakleion GRC 116178 +2407 Kallithea GRC 114233 +2408 Larisa GRC 113090 +2908 Cajamarca PER 108009 +3002 Besançon FRA 117733 +3003 Caen FRA 113987 +3004 Orléans FRA 113126 +3005 Mulhouse FRA 110359 +3006 Rouen FRA 106592 +3007 Boulogne-Billancourt FRA 106367 +3008 Perpignan FRA 105115 +3009 Nancy FRA 103605 +3411 Ceyhan TUR 102412 +3567 Carúpano VEN 119639 +3568 Catia La Mar VEN 117012 +3571 Calabozo VEN 107146 +3786 Cam Ranh VNM 114041 +3792 Tartu EST 101246 +4002 Carrollton USA 109576 +4027 Cape Coral USA 102286 +4032 Cambridge USA 101355 +SHOW STATUS LIKE 'Handler_read_%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 2 +Handler_read_next 385 +Handler_read_prev 0 +Handler_read_rnd 377 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +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 +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 1 +Handler_read_next 59 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +set optimizer_switch='index_merge=off'; +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 index condition; Using where +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 1 +Handler_read_next 59 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +set optimizer_switch=@save_optimizer_switch; DROP DATABASE world; use test; CREATE TABLE t1 (
=== modified file 'mysql-test/t/range_vs_index_merge.test' --- a/mysql-test/t/range_vs_index_merge.test 2012-03-21 00:03:28 +0000 +++ b/mysql-test/t/range_vs_index_merge.test 2012-11-01 21:54:33 +0000 @@ -675,6 +675,64 @@ $cond;
set optimizer_switch=@save_optimizer_switch; + +--echo # +--echo # Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n +--echo # (LP bug #637962) +--echo # + +DROP INDEX CountryPopulation ON City; +DROP INDEX CountryName ON City; +DROP INDEX CityName on City; + +CREATE INDEX Name ON City(Name); +CREATE INDEX Population ON City(Population); + + +--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); +FLUSH STATUS; +SELECT * FROM City + WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) + AND (Population >= 100000 AND Population < 120000); +SHOW STATUS LIKE 'Handler_read_%'; + + +--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_switch='index_merge=off'; + +--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_switch=@save_optimizer_switch;
DROP DATABASE world;
=== modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2012-10-10 05:21:22 +0000 +++ b/sql/sql_select.cc 2012-11-01 21:54:33 +0000 @@ -18079,15 +18079,18 @@ */
if (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE || - quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT || + quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT || quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION || quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) - goto use_filesort; - ref_key= select->quick->index; - ref_key_parts= select->quick->used_key_parts; + ref_key= MAX_KEY; + else + { + ref_key= select->quick->index; + ref_key_parts= select->quick->used_key_parts; + } }
- if (ref_key >= 0) + if (ref_key >= 0 && ref_key != MAX_KEY) { /* We come here when there is a REF key. @@ -18229,7 +18232,8 @@ else keys= usable_keys;
- if (ref_key >= 0 && table->covering_keys.is_set(ref_key)) + if (ref_key >= 0 && ref_key != MAX_KEY && + table->covering_keys.is_set(ref_key)) ref_key_quick_rows= table->quick_rows[ref_key];
read_time= join->best_positions[tablenr].read_time;
_______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
-- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog
On 11/02/2012 08:22 AM, Sergei Petrunia wrote:
Hello Igor,
The fix seems to be ok to push.
Which version do you want to push it into? This is not a crash, should we really push it into 5.3, or to 5.5?
The bug was reported for 5.3. So I intend to to push it to 5.3. Both 5.3 and 5.5 are in GA anyway. Regards, Igor.
On Thu, Nov 01, 2012 at 02:54:34PM -0700, Igor Babaev wrote:
At file:///home/igor/maria/maria-5.3-mdev585/
------------------------------------------------------------ revno: 3589 revision-id: igor@askmonty.org-20121101215433-ctfm3zfhc6uhuy3i parent: psergey@askmonty.org-20121031053425-kfn0l7yj6z7jtnqw committer: Igor Babaev <igor@askmonty.org> branch nick: maria-5.3-mdev585 timestamp: Thu 2012-11-01 14:54:33 -0700 message: Fixed bug mdev-585 (LP bug #637962) If, when executing a query with ORDER BY col LIMIT n, the optimizer chose an index-merge scan to access the table containing col while there existed an index defined over col then optimizer did not consider the possibility of using an alternative range scan by this index to avoid filesort. This could cause a performance degradation if the optimizer flag index_merge was set up to 'on'.
=== modified file 'mysql-test/r/range_vs_index_merge.result' --- a/mysql-test/r/range_vs_index_merge.result 2012-03-21 00:03:28 +0000 +++ b/mysql-test/r/range_vs_index_merge.result 2012-11-01 21:54:33 +0000 @@ -1221,6 +1221,150 @@ Seattle USA 563374 Caracas VEN 1975294 set optimizer_switch=@save_optimizer_switch; +# +# Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n +# (LP bug #637962) +# +DROP INDEX CountryPopulation ON City; +DROP INDEX CountryName ON City; +DROP INDEX CityName on City; +CREATE INDEX Name ON City(Name); +CREATE INDEX Population ON City(Population); +EXPLAIN +SELECT * FROM City +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) +AND (Population >= 100000 AND Population < 120000); +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 +FLUSH STATUS; +SELECT * FROM City +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) +AND (Population >= 100000 AND Population < 120000); +ID Name Country Population +384 Cabo Frio BRA 119503 +387 Camaragibe BRA 118968 +403 Catanduva BRA 107761 +412 Cachoeirinha BRA 103240 +508 Watford GBR 113080 +509 Ipswich GBR 114000 +510 Slough GBR 112000 +511 Exeter GBR 111000 +512 Cheltenham GBR 106000 +513 Gloucester GBR 107000 +514 Saint Helens GBR 106293 +515 Sutton Coldfield GBR 106001 +516 York GBR 104425 +517 Oldham GBR 103931 +518 Basildon GBR 100924 +519 Worthing GBR 100000 +635 Mallawi EGY 119283 +636 Bilbays EGY 113608 +637 Mit Ghamr EGY 101801 +638 al-Arish EGY 100447 +701 Tarragona ESP 113016 +702 Lleida (Lérida) ESP 112207 +703 Jaén ESP 109247 +704 Ourense (Orense) ESP 109120 +705 Mataró ESP 104095 +706 Algeciras ESP 103106 +707 Marbella ESP 101144 +759 Gonder ETH 112249 +869 Cabuyao PHL 106630 +870 Calapan PHL 105910 +873 Cauayan PHL 103952 +903 Serekunda GMB 102600 +909 Sohumi GEO 111700 +913 Tema GHA 109975 +914 Sekondi-Takoradi GHA 103653 +924 Villa Nueva GTM 101295 +1844 Cape Breton CAN 114733 +1847 Cambridge CAN 109186 +2406 Herakleion GRC 116178 +2407 Kallithea GRC 114233 +2408 Larisa GRC 113090 +2908 Cajamarca PER 108009 +3002 Besançon FRA 117733 +3003 Caen FRA 113987 +3004 Orléans FRA 113126 +3005 Mulhouse FRA 110359 +3006 Rouen FRA 106592 +3007 Boulogne-Billancourt FRA 106367 +3008 Perpignan FRA 105115 +3009 Nancy FRA 103605 +3411 Ceyhan TUR 102412 +3567 Carúpano VEN 119639 +3568 Catia La Mar VEN 117012 +3571 Calabozo VEN 107146 +3786 Cam Ranh VNM 114041 +3792 Tartu EST 101246 +4002 Carrollton USA 109576 +4027 Cape Coral USA 102286 +4032 Cambridge USA 101355 +SHOW STATUS LIKE 'Handler_read_%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 2 +Handler_read_next 385 +Handler_read_prev 0 +Handler_read_rnd 377 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +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 +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 1 +Handler_read_next 59 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +set optimizer_switch='index_merge=off'; +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 index condition; Using where +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 1 +Handler_read_next 59 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +set optimizer_switch=@save_optimizer_switch; DROP DATABASE world; use test; CREATE TABLE t1 (
=== modified file 'mysql-test/r/range_vs_index_merge_innodb.result' --- a/mysql-test/r/range_vs_index_merge_innodb.result 2012-03-21 00:03:28 +0000 +++ b/mysql-test/r/range_vs_index_merge_innodb.result 2012-11-01 21:54:33 +0000 @@ -1222,6 +1222,150 @@ Seattle USA 563374 Caracas VEN 1975294 set optimizer_switch=@save_optimizer_switch; +# +# Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n +# (LP bug #637962) +# +DROP INDEX CountryPopulation ON City; +DROP INDEX CountryName ON City; +DROP INDEX CityName on City; +CREATE INDEX Name ON City(Name); +CREATE INDEX Population ON City(Population); +EXPLAIN +SELECT * FROM City +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) +AND (Population >= 100000 AND Population < 120000); +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 +FLUSH STATUS; +SELECT * FROM City +WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) +AND (Population >= 100000 AND Population < 120000); +ID Name Country Population +384 Cabo Frio BRA 119503 +387 Camaragibe BRA 118968 +403 Catanduva BRA 107761 +412 Cachoeirinha BRA 103240 +508 Watford GBR 113080 +509 Ipswich GBR 114000 +510 Slough GBR 112000 +511 Exeter GBR 111000 +512 Cheltenham GBR 106000 +513 Gloucester GBR 107000 +514 Saint Helens GBR 106293 +515 Sutton Coldfield GBR 106001 +516 York GBR 104425 +517 Oldham GBR 103931 +518 Basildon GBR 100924 +519 Worthing GBR 100000 +635 Mallawi EGY 119283 +636 Bilbays EGY 113608 +637 Mit Ghamr EGY 101801 +638 al-Arish EGY 100447 +701 Tarragona ESP 113016 +702 Lleida (Lérida) ESP 112207 +703 Jaén ESP 109247 +704 Ourense (Orense) ESP 109120 +705 Mataró ESP 104095 +706 Algeciras ESP 103106 +707 Marbella ESP 101144 +759 Gonder ETH 112249 +869 Cabuyao PHL 106630 +870 Calapan PHL 105910 +873 Cauayan PHL 103952 +903 Serekunda GMB 102600 +909 Sohumi GEO 111700 +913 Tema GHA 109975 +914 Sekondi-Takoradi GHA 103653 +924 Villa Nueva GTM 101295 +1844 Cape Breton CAN 114733 +1847 Cambridge CAN 109186 +2406 Herakleion GRC 116178 +2407 Kallithea GRC 114233 +2408 Larisa GRC 113090 +2908 Cajamarca PER 108009 +3002 Besançon FRA 117733 +3003 Caen FRA 113987 +3004 Orléans FRA 113126 +3005 Mulhouse FRA 110359 +3006 Rouen FRA 106592 +3007 Boulogne-Billancourt FRA 106367 +3008 Perpignan FRA 105115 +3009 Nancy FRA 103605 +3411 Ceyhan TUR 102412 +3567 Carúpano VEN 119639 +3568 Catia La Mar VEN 117012 +3571 Calabozo VEN 107146 +3786 Cam Ranh VNM 114041 +3792 Tartu EST 101246 +4002 Carrollton USA 109576 +4027 Cape Coral USA 102286 +4032 Cambridge USA 101355 +SHOW STATUS LIKE 'Handler_read_%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 2 +Handler_read_next 385 +Handler_read_prev 0 +Handler_read_rnd 377 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +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 +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 1 +Handler_read_next 59 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +set optimizer_switch='index_merge=off'; +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 index condition; Using where +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 1 +Handler_read_next 59 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +set optimizer_switch=@save_optimizer_switch; DROP DATABASE world; use test; CREATE TABLE t1 (
=== modified file 'mysql-test/t/range_vs_index_merge.test' --- a/mysql-test/t/range_vs_index_merge.test 2012-03-21 00:03:28 +0000 +++ b/mysql-test/t/range_vs_index_merge.test 2012-11-01 21:54:33 +0000 @@ -675,6 +675,64 @@ $cond;
set optimizer_switch=@save_optimizer_switch; + +--echo # +--echo # Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n +--echo # (LP bug #637962) +--echo # + +DROP INDEX CountryPopulation ON City; +DROP INDEX CountryName ON City; +DROP INDEX CityName on City; + +CREATE INDEX Name ON City(Name); +CREATE INDEX Population ON City(Population); + + +--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); +FLUSH STATUS; +SELECT * FROM City + WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H')) + AND (Population >= 100000 AND Population < 120000); +SHOW STATUS LIKE 'Handler_read_%'; + + +--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_switch='index_merge=off'; + +--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_switch=@save_optimizer_switch;
DROP DATABASE world;
=== modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2012-10-10 05:21:22 +0000 +++ b/sql/sql_select.cc 2012-11-01 21:54:33 +0000 @@ -18079,15 +18079,18 @@ */
if (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE || - quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT || + quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT || quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION || quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) - goto use_filesort; - ref_key= select->quick->index; - ref_key_parts= select->quick->used_key_parts; + ref_key= MAX_KEY; + else + { + ref_key= select->quick->index; + ref_key_parts= select->quick->used_key_parts; + } }
- if (ref_key >= 0) + if (ref_key >= 0 && ref_key != MAX_KEY) { /* We come here when there is a REF key. @@ -18229,7 +18232,8 @@ else keys= usable_keys;
- if (ref_key >= 0 && table->covering_keys.is_set(ref_key)) + if (ref_key >= 0 && ref_key != MAX_KEY && + table->covering_keys.is_set(ref_key)) ref_key_quick_rows= table->quick_rows[ref_key];
read_time= join->best_positions[tablenr].read_time;
_______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
participants (2)
-
Igor Babaev -
Sergei Petrunia