[MDEV-4220] extended_keys=on makes eq_ref access be ref access Created: 2013-03-01  Updated: 2013-03-04  Resolved: 2013-03-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.29
Fix Version/s: 10.0.2, 5.5.30

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None


 Description   

If one turns on extended keys, the optimizer starts use ref access where it used to use eq_ref access.

eq_ref is somewhat slower than 'equivalent' ref access, but that is only part of the problem. ORDER BY optimization checks if eq_ref is used, and if yes, enables certain strategies (see sql_select.cc, eq_ref_table()).

Testcase:

--source include/have_innodb.inc
 
create table t20 (a int not null) engine=innodb;
 
 
 
insert into t20 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
 
 
create table t21 (pk int primary key, a int not null, b int, unique(a))engine=innodb;
 
 
 
insert into t21
 
select 
  A.a + 10 * B.a, A.a + 10 * B.a, A.a + 10 * B.a 
from t20 A, t20 B;
 
 
 
set optimizer_switch='extended_keys=on';
 
explain select * from t20, t21 where t21.a=t20.a;
 
set optimizer_switch='extended_keys=off';
 
explain select * from t20, t21 where t21.a=t20.a;

its output:

create table t20 (a int not null) engine=innodb;
 
insert into t20 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
create table t21 (pk int primary key, a int not null, b int, unique(a))engine=innodb;
 
insert into t21
 
select 
A.a + 10 * B.a, A.a + 10 * B.a, A.a + 10 * B.a 
from t20 A, t20 B;
 
set optimizer_switch='extended_keys=on';
 
explain select * from t20, t21 where t21.a=t20.a;
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
1	SIMPLE	t20	ALL	NULL	NULL	NULL	NULL	10	
1	SIMPLE	t21	ref	a	a	4	test.t20.a	1	
set optimizer_switch='extended_keys=off';
 
explain select * from t20, t21 where t21.a=t20.a;
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
1	SIMPLE	t20	ALL	NULL	NULL	NULL	NULL	10	
1	SIMPLE	t21	eq_ref	a	a	4	test.t20.a	1



 Comments   
Comment by Igor Babaev [ 2013-03-04 ]

This is a regression bug introduced by the patch for mdev-3851 that appeared in mariadb 5.5.28.a.

Comment by Igor Babaev [ 2013-03-04 ]

The fix for the bug was pushed into the 5.5 tree on 2013-03-03. It will appear in 5.5.30.

Generated at Thu Feb 08 06:54:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.