maria-developers team mailing list archive

[Igor Babaev <igor@xxxxxxxxxxxx>]

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@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
>