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