maria-developers team mailing list archive

Thread
Date
Re: [Commits] Rev 3589: Fixed bug mdev-585 (LP bug #637962) in file:///home/igor/maria/maria-5.3-mdev585/

To: maria-developers@xxxxxxxxxxxxxxxxxxx, igor@xxxxxxxxxxxx
From: Sergei Petrunia <psergey@xxxxxxxxxxxx>
Date: Fri, 2 Nov 2012 19:22:28 +0400
Cc: commits@xxxxxxxxxxx
In-reply-to: <20121101215434.960B5280A80@sophia.site>
User-agent: Mutt/1.5.18 (2008-05-17)
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@xxxxxxxxxxxx-20121101215433-ctfm3zfhc6uhuy3i
> parent: psergey@xxxxxxxxxxxx-20121031053425-kfn0l7yj6z7jtnqw
> committer: Igor Babaev <igor@xxxxxxxxxxxx>
> 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@xxxxxxxxxxx
> 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
Follow ups

Re: [Commits] Rev 3589: Fixed bug mdev-585 (LP bug #637962) in file:///home/igor/maria/maria-5.3-mdev585/
From: Igor Babaev, 2012-11-02