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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp