Hi Jocelyn! :)

2014-12-01 10:57 GMT-02:00 jocelyn fournier <jocelyn.fournier@gmail.com>:

Hi Roberto,

What are the cardinalities of the index in this table ? (SHOW INDEXES FROM est_mov).

well i executed optimize and analyze before select...

i will explain more about the problem...

i was using mariadb 10.0.5 (or 10.0.12 i don't remember now), and optimizer was using the right index ("rendimento"), after upgrading to 10.0.14 and 10.0.15, optimizer is using "item" index, the result is a slower query (it's a problem, but it's not related with my doubt...)

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).

yes , i tried with the replication server (running aria/myisam) and with the master server (running innodb), both have the same optimizer choise (the wrong index) even with analyze and optimizer before the "select"

As a dev, if I'm sure of what I'm doing, I would use STRAIGHT_JOIN / USE INDEX.

yeap, as developer i can change the query to FORCE INDEX (rendimento)

but, my doubt is, why optimizer selecting the "wrong" (slower) index?

to explain this i'm executing FORCE INDEX() in each possible index, but i don't know if it's the 'best' way to get information

HTH,
Jocelyn

Roberto Spadim
SPAEmpresarial

Eng. Automação e Controle