maria-discuss team mailing list archive

[jocelyn fournier <jocelyn.fournier@xxxxxxxxx>]

Hi Roberto,

What are the cardinalities of the index in this table ? (SHOW INDEXES 
FROM est_mov).
As a DBA, if cardinalities are wrong, I would first try to run an 
ANALYZE TABLE command (and if it's InnoDB check if the 
innodb_stats_sample_pages is enough).
As a dev, if I'm sure of what I'm doing, I would use STRAIGHT_JOIN / USE 
INDEX.

HTH,
  Jocelyn




Le 01/12/2014 13:50, Roberto Spadim a écrit :
> 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
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp