[Maria-discuss] new mdev about better explain
Hi guys, any help is wellcome here the problem: why optimizer chose a "bad" index? an example about this problem is MDEV-7125 i have this EXPLAIN output: id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra 1|SIMPLE|c|const|PRIMARY,NewIndex|PRIMARY|265|const,const|1|Distinct; Using temporary 1|SIMPLE|b|range|PRIMARY,id,plano_conta_numero,cod_busca|cod_busca|265|(NULL)|49|Using index condition; Distinct *1|SIMPLE|a|ref|cfop,item,transferencias,rendimento,estoque,giro|item|6|const,19_org.b.plano_conta_id_red|3347|Using where; Distinct* 1|SIMPLE|d|eq_ref|PRIMARY,NewIndex|PRIMARY|265|19_org.a.estoque_entrada_org,const|1|Using index condition; Using where; Distinct --- check the row from table "a" 1|SIMPLE|a|ref|*cfop,item,transferencias,rendimento,estoque,giro*|item|6|const,19_org.b.plano_conta_id_red|3347|Using where; Distinct optimizer found 5 possible index, and chose the "item" index, instead of "rendimento" index, it's not the best index, but i don't know why optimizer prefer "item" instead of "rendimento" to "solve" (explain) this optimizer choise i execute force index in each index and get output from explain... example EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (cfop) ... EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (item) ... EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (transferencias) ... EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (rendimento) ... EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (estoque) ... EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (giro) ... and compare each output to know why it chose the index 'item' as 'best' option could be possible a better explain from optimizer? something like { 'item': {'key_len':1234, 'ref rows':'blablalba', 'filtered' 99, 'extra':'blabla'}, 'transferencias': {'key_len':1234, 'ref rows':'blablalba', 'filtered' 99, 'extra':'blabla'}, 'rendimento': {'key_len':1234, 'ref rows':'blablalba', 'filtered' 99, 'extra':'blabla'}, 'giro': {'key_len':1234, 'ref rows':'blablalba', 'filtered' 99, 'extra':'blabla'} } i don't know if optimizer have this information, but it's relevant? could we implement a better explain? what others users (dba/developer) do when they have this problem (optimizer selecting the wrong index)? thanks guys -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
Hi Roberto, What are the cardinalities of the index in this table ? (SHOW INDEXES FROM est_mov). 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). As a dev, if I'm sure of what I'm doing, I would use STRAIGHT_JOIN / USE INDEX. HTH, Jocelyn Le 01/12/2014 13:50, Roberto Spadim a écrit :
Hi guys, any help is wellcome here
the problem: why optimizer chose a "bad" index?
an example about this problem is MDEV-7125 i have this EXPLAIN output:
id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra 1|SIMPLE|c|const|PRIMARY,NewIndex|PRIMARY|265|const,const|1|Distinct; Using temporary 1|SIMPLE|b|range|PRIMARY,id,plano_conta_numero,cod_busca|cod_busca|265|(NULL)|49|Using index condition; Distinct *1|SIMPLE|a|ref|cfop,item,transferencias,rendimento,estoque,giro|item|6|const,19_org.b.plano_conta_id_red|3347|Using where; Distinct* 1|SIMPLE|d|eq_ref|PRIMARY,NewIndex|PRIMARY|265|19_org.a.estoque_entrada_org,const|1|Using index condition; Using where; Distinct
--- check the row from table "a" 1|SIMPLE|a|ref|*cfop,item,transferencias,rendimento,estoque,giro*|item|6|const,19_org.b.plano_conta_id_red|3347|Using where; Distinct
optimizer found 5 possible index, and chose the "item" index, instead of "rendimento" index, it's not the best index, but i don't know why optimizer prefer "item" instead of "rendimento"
to "solve" (explain) this optimizer choise i execute force index in each index and get output from explain... example
EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (cfop) ... EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (item) ... EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (transferencias) ... EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (rendimento) ... EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (estoque) ... EXPLAIN SELECT ... FROM est_mov AS a FORCE INDEX (giro) ...
and compare each output to know why it chose the index 'item' as 'best' option
could be possible a better explain from optimizer? something like
{ 'item': {'key_len':1234, 'ref rows':'blablalba', 'filtered' 99, 'extra':'blabla'}, 'transferencias': {'key_len':1234, 'ref rows':'blablalba', 'filtered' 99, 'extra':'blabla'}, 'rendimento': {'key_len':1234, 'ref rows':'blablalba', 'filtered' 99, 'extra':'blabla'}, 'giro': {'key_len':1234, 'ref rows':'blablalba', 'filtered' 99, 'extra':'blabla'} }
i don't know if optimizer have this information, but it's relevant? could we implement a better explain? what others users (dba/developer) do when they have this problem (optimizer selecting the wrong index)?
thanks guys
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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
----- Original Message -----
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...
How novel. Except you didn't provided the asked for information.
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 -- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.
Hi Daniel! 2014-12-01 20:07 GMT-02:00 Daniel Black <daniel.black@openquery.com>:
----- Original Message -----
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...
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
-- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
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
participants (4)
-
Daniel Black -
Jean Weisbuch -
jocelyn fournier -
Roberto Spadim