MySQL Bugs: #61051: index optimization

Bug #61051	index optimization
Submitted:	4 May 2011 7:10	Modified:	29 Jan 2013 9:31
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
hi guys, i'm continuing Bug #61049

i don't know why, but should query 1 be executed faster than query 2
in some big queries with many tables, it's a problem since optimizer preffer smaller index, instead bigger index
smaller = without information about others fields in query
bigger = with all fields used in query (this don't need to search index and data files)

i'm using myisam

How to repeat:
1)
CREATE TABLE `estoque_itens` (
  `plano_conta_id` bigint(20) NOT NULL DEFAULT '0',
  `plano_conta_id_red` bigint(20) NOT NULL DEFAULT '0',
  `codigo_busca` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`plano_conta_id`,`plano_conta_id_red`) USING BTREE,
  UNIQUE KEY `codigo_busca` (`plano_conta_id`,`codigo_busca`),
  UNIQUE KEY `cb` (`codigo_busca`,`plano_conta_id`,`plano_conta_id_red`)
) engine=myisam

2)
populate it

3)
query 1:
select codigo_busca,plano_conta_id_red
from estoque_itens force key (cb)
where plano_conta_id=50 and codigo_busca='91526'

query 2:
select codigo_busca,plano_conta_id_red
from estoque_itens
where plano_conta_id=50 and codigo_busca='91526'

query 3:
select codigo_busca,plano_conta_id_red
from estoque_itens
where plano_conta_id=50 and codigo_busca='91526' and plano_conta_id_red=50

4)
explain 1) type: ref, extra: using where, using index, index(cb)
explain 2) type: const, extra:, index(codigo_busca)
explain 3) type: const, extra:, index(PRIMARY KEY)

Suggested fix:
maybe a optimizer option? preffer 'big' index, instead 'smaller' index, since estimated rows have same value

since cb index can allow more rows than codigo_busca index, the 'where' part of extra is ok
but check query 3... why preffer PRIMARY KEY, if it don't have information about plano_conta_id_red? and cb index have this information!?

Can you, please, execute the following sequence of statements:

show status like 'Handler_read%';
<your query 1>;
show status like 'Handler_read%';
<your query 2>;
show status like 'Handler_read%';
<your query 3>
show status like 'Handler_read%';

on your real data. I need to know the results of each show status above, and execution time for each of the queries. Then we shell see when more work is done by the server.

1)show status like 'Handler_read%';
"Handler_read_first","0"
"Handler_read_key","0"
"Handler_read_next","0"
"Handler_read_prev","0"
"Handler_read_rnd","0"
"Handler_read_rnd_next","47"

2)select codigo_busca,plano_conta_id_red from estoque_itens force key (cb) where plano_conta_id=50 and codigo_busca='91526'

3)show status like 'Handler_read%';
"Handler_read_first","2"
"Handler_read_key","2"
"Handler_read_next","1"
"Handler_read_prev","0"
"Handler_read_rnd","0"
"Handler_read_rnd_next","323"

4)select codigo_busca,plano_conta_id_red from estoque_itens where plano_conta_id=50 and codigo_busca='91526'

5)show status like 'Handler_read%';
"Handler_read_first","2"
"Handler_read_key","3"
"Handler_read_next","1"
"Handler_read_prev","0"
"Handler_read_rnd","0"
"Handler_read_rnd_next","323"

6)select codigo_busca,plano_conta_id_red from estoque_itens where plano_conta_id=50 and codigo_busca='91526' and plano_conta_id_red=50

7)show status like 'Handler_read%';
"Handler_read_first","2"
"Handler_read_key","4"
"Handler_read_next","1"
"Handler_read_prev","0"
"Handler_read_rnd","0"
"Handler_read_rnd_next","323"

explain results:
1) explain select codigo_busca,plano_conta_id_red from estoque_itens force key (cb) where plano_conta_id=50 and codigo_busca='91526'

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
"1","SIMPLE","estoque_itens","ref","cb","cb","265","const,const","1","Using where; Using index"

2) explain select codigo_busca,plano_conta_id_red from estoque_itens where plano_conta_id=50 and codigo_busca='91526'

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
"1","SIMPLE","estoque_itens","const","PRIMARY,plano_conta_numero,codigo_busca,cb","codigo_busca","265","const,const","1",""

3) explain select codigo_busca,plano_conta_id_red from estoque_itens where plano_conta_id=50 and codigo_busca='91526' and plano_conta_id_red=50

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
"1","SIMPLE","estoque_itens","const","PRIMARY,plano_conta_numero,codigo_busca,cb","PRIMARY","16","const,const","1",""

hum... i'm using mariadb 5.2.5
i will change table to myisam and test again and post handlers results... just some minutes

now with myisam:

1) show status like 'Handler_read%';
"Handler_read_first","2"
"Handler_read_key","1"
"Handler_read_next","0"
"Handler_read_prev","0"
"Handler_read_rnd","0"
"Handler_read_rnd_next","15"

2)select codigo_busca,plano_conta_id_red from estoque_itens force key (cb) where plano_conta_id=50 and codigo_busca='91526'

3) show status like 'Handler_read%';
"Handler_read_first","2"
"Handler_read_key","2"
"Handler_read_next","1"
"Handler_read_prev","0"
"Handler_read_rnd","0"
"Handler_read_rnd_next","15"

4)select codigo_busca,plano_conta_id_red from estoque_itens where plano_conta_id=50 and codigo_busca='91526'

5) show status like 'Handler_read%';
"Handler_read_first","2"
"Handler_read_key","3"
"Handler_read_next","1"
"Handler_read_prev","0"
"Handler_read_rnd","0"
"Handler_read_rnd_next","15"

6)select codigo_busca,plano_conta_id_red from estoque_itens where plano_conta_id=50 and codigo_busca='91526' and plano_conta_id_red=50

7) show status like 'Handler_read%';
"Handler_read_first","2"
"Handler_read_key","4"
"Handler_read_next","1"
"Handler_read_prev","0"
"Handler_read_rnd","0"
"Handler_read_rnd_next","15"

i think it's related to mariadb - aria engine, i'm wrong? maybe optimizer??

@Roberto, 

There seems to be some minor issues with the indexes picked by the optimizer here. Thank you for the bug report.