maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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