id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra
1|SIMPLE|c|const|PRIMARY,NewIndex|PRIMARY|265|const,const|1|Distinct; Using temporary
1|SIMPLE|b|range|PRIMARY,id,plano_conta_numero,cod_busca|cod_busca|265|(NULL)|49|Using index condition; Distinct
1|SIMPLE|a|ref|cfop,item,transferencias,rendimento,estoque,giro|item|6|const,19_org.b.plano_conta_id_red|3347|Using where; Distinct
1|SIMPLE|d|eq_ref|PRIMARY,NewIndex|PRIMARY|265|19_org.a.estoque_entrada_org,const|1|Using index condition; Using where; Distinct
---
check the row from table "a"
1|SIMPLE|a|ref|cfop,item,transferencias,rendimento,estoque,giro|item|6|const,19_org.b.plano_conta_id_red|3347|Using where; Distinct
optimizer found 5 possible index, and chose the "item" index, instead of "rendimento" index, it's not the best index, but i don't know why optimizer prefer "item" instead of "rendimento"
to "solve" (explain) this optimizer choise i execute force index in each index and get output from explain... example
EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (cfop) ...
EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (item) ...
EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (transferencias) ...
EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (rendimento) ...
EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (estoque) ...
EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (giro) ...
and compare each output to know why it chose the index 'item' as 'best' option
could be possible a better explain from optimizer?
something like
{
'item': {'key_len':1234, 'ref rows':'blablalba', 'filtered' 99, 'extra':'blabla'},
'transferencias': {'key_len':1234, 'ref rows':'blablalba', 'filtered' 99, 'extra':'blabla'},
'rendimento': {'key_len':1234, 'ref rows':'blablalba', 'filtered' 99, 'extra':'blabla'},
'giro': {'key_len':1234, 'ref rows':'blablalba', 'filtered' 99, 'extra':'blabla'}
}
i don't know if optimizer have this information, but it's relevant? could we implement a better explain?
what others users (dba/developer) do when they have this problem (optimizer selecting the wrong index)?
thanks guys
--
Roberto Spadim
SPAEmpresarial
Eng. Automação e Controle