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: [email protected] > parent: [email protected] > committer: Igor Babaev <[email protected]> > 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 > [email protected] > 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 _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
