maria-discuss team mailing list archive

[Jean Weisbuch <jean@xxxxxxxxxx>]

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@xxxxxxxxxxxxx 
> <mailto:daniel.black@xxxxxxxxxxxxx>>:
>
>
>
>     ----- Original Message -----
>     > Hi Jocelyn! :)
>     >
>     > 2014-12-01 10:57 GMT-02:00 jocelyn fournier <
>     jocelyn.fournier@xxxxxxxxx <mailto: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...
>
>     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