maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

Hi guys, any help is wellcome here

the problem:   why optimizer chose a "bad" index?

an example about this problem is MDEV-7125
i have this EXPLAIN output:

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