maria-discuss team mailing list archive
Mailing list archive
Re: new mdev about better explain
Hi Jocelyn! :)
2014-12-01 10:57 GMT-02:00 jocelyn fournier <jocelyn.fournier@xxxxxxxxx>:
> Hi Roberto,
> What are the cardinalities of the index in this table ? (SHOW INDEXES FROM
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
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
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
Eng. Automação e Controle