By any chance, have you tried different "optimizer_switch" values? I think that "join_cache_level" might also have an influence on the index used by the optimizer. ps: when you are pasting the output from "SHOW INDEX" or similar, you should set this text in "preformated" mode on your mail client so the tabulations doesn't get removed by it, rendering the output hard to read. Le 01/12/2014 23:15, Roberto Spadim a écrit :
Hi Daniel!
2014-12-01 20:07 GMT-02:00 Daniel Black <daniel.black@openquery.com <mailto:daniel.black@openquery.com>>:
----- Original Message ----- > Hi Jocelyn! :) > > 2014-12-01 10:57 GMT-02:00 jocelyn fournier < jocelyn.fournier@gmail.com <mailto: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...
How novel. Except you didn't provided the asked for information.
ops! that's the myisam output of show index from table
|| *Table* || *Non_unique* || *Key_name* || *Seq_in_index* || *Column_name* || *Collation* || *Cardinality* || *Sub_part* || *Packed* || *Null* || *Index_type* || *Comment* || *Index_comment* || || est_mov_myisam || 1 || rendimento || 1 || rendimento || A || 3 || || || || BTREE || || || || est_mov_myisam || 1 || rendimento || 2 || lote_rendimento_data || A || 6078 || || || || BTREE || || || || est_mov_myisam || 1 || rendimento || 3 || item_id_red || A || 832974 || || || || BTREE || || ||
|| est_mov_myisam || 1 || item || 1 || item_id || A || 10 || || || || BTREE || || || || est_mov_myisam || 1 || item || 2 || item_id_red || A || 8786 || || || || BTREE || || || || est_mov_myisam || 1 || item || 3 || data_estoque || A || 14577050 || || || || BTREE || || ||
> 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...)
Could also be related to https://mariadb.atlassian.net/browse/MDEV-7084
it was 10.0.12