[MDEV-7125] selecting wrong index - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Won't Fix
Affects Version/s: 10.0(EOL)
Fix Version/s: N/A
Component/s: OTHER
Labels:
None

Description

hi guys, mariadb-10.0.12 is selecting the wrong index in some cases:

the difference from both selects is FORCE INDEX (rendimento)
this one give better results since "lote_rendimento_data" (column) have less values then (item_id,item_id_red) columns

i don't know if it's a big problem, but this give some slower results, running optimize don't help, running analyze don't help too, i'm not using histograms

explain

SELECT DISTINCT "" AS descricao,'+' AS add_sub,"PRODUTOS" AS tabela_preco,d.plano_conta_numero AS centro_numero,d.descricao AS centro_descricao,a.quant AS quant_real,a.pecas AS pecas_real,a.pliq AS pliq_real,a.pbruto AS pbruto_real,a.vliq AS vliq_real,a.vbruto AS vbruto_real,a.quant,a.pecas,a.pliq AS pliq,a.pbruto,a.vliq,a.vbruto,a.custo,a.lote_rendimento_data,a.lote_rendimento_numero,a.oe_tipo,c.plano_conta_numero AS cfop_numero,c.descricao AS cfop_descricao,b.plano_conta_numero AS item_numero,b.descricao AS item_descricao,b.codigo_busca AS item_codigo_busca ,a.unidade_id,a.lote_tipo,a.lote_spa,a.item_id,a.item_id_red,a.mov_id,a.oe,a.oe_seq,(b.plano_conta_id || ";" || b.plano_conta_numero) AS tmp_group,(b.plano_conta_id || ";" || b.codigo_busca) AS tmp_group_cb,60 AS ordem,b.producao_faixa_pliq_1 AS kg_cadastro_1,b.producao_faixa_pliq_2 AS kg_cadastro_2,'cadastro' AS kg_pc_tipo,0 AS kg_pc_1,0 AS kg_pc_2,0 AS valor_venda_un,'un' AS valor_venda_grandeza,0 AS valor_venda_grandeza_quant,(SELECT valor_custo_grandeza_quant FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza_quant,(SELECT valor_custo_grandeza FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza,(SELECT mc_minima FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS mc_minima,a.lote_sif_data,a.lote_sif,a.data_estoque FROM est_mov AS a force index (rendimento), estoque_itens AS b, cfop_itens AS c , organograma AS d WHERE a.oe_tipo="tr" AND d.plano_conta_id=a.estoque_entrada_org AND d.plano_conta_id_red=a.estoque_entrada_org_red AND a.estoque_entrada_un=1001 AND d.plano_conta_numero = "3.05.01.01" AND a.item_id=67 AND b.plano_conta_id=a.item_id AND b.plano_conta_id_red=a.item_id_red AND b.codigo_busca LIKE "06%" AND c.plano_conta_id=a.cfop_id AND c.plano_conta_id_red=a.cfop_id_red AND c.plano_conta_id=16 AND c.plano_conta_numero = "0.995" AND a.lote_rendimento_data>="2014-11-13" AND a.lote_rendimento_data<"2014-11-17" AND a.rendimento='Y' AND a.lote_rendimento_numero>=3 AND a.lote_rendimento_numero<=3;

this one takes 1,997 seconds

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	c	const	PRIMARY,NewIndex	PRIMARY	265	const,const	1	Using temporary
1	PRIMARY	a	range	rendimento	rendimento	4		171754	Using where
1	PRIMARY	d	eq_ref	PRIMARY,NewIndex	PRIMARY	265	19_org.a.estoque_entrada_org,const	1	Using index condition; Using where
1	PRIMARY	b	eq_ref	PRIMARY,id,plano_conta_numero,cod_busca	id	16	const,19_org.a.item_id_red	1	Using index condition; Using where
4	DEPENDENT SUBQUERY	unidade_itens_custo	eq_ref	PRIMARY	PRIMARY	24	19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red	1	Using index condition
3	DEPENDENT SUBQUERY	unidade_itens_custo	eq_ref	PRIMARY	PRIMARY	24	19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red	1	Using index condition
2	DEPENDENT SUBQUERY	unidade_itens_custo	eq_ref	PRIMARY	PRIMARY	24	19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red	1	Using index condition

explain

SELECT DISTINCT "" AS descricao,'+' AS add_sub,"PRODUTOS" AS tabela_preco,d.plano_conta_numero AS centro_numero,d.descricao AS centro_descricao,a.quant AS quant_real,a.pecas AS pecas_real,a.pliq AS pliq_real,a.pbruto AS pbruto_real,a.vliq AS vliq_real,a.vbruto AS vbruto_real,a.quant,a.pecas,a.pliq AS pliq,a.pbruto,a.vliq,a.vbruto,a.custo,a.lote_rendimento_data,a.lote_rendimento_numero,a.oe_tipo,c.plano_conta_numero AS cfop_numero,c.descricao AS cfop_descricao,b.plano_conta_numero AS item_numero,b.descricao AS item_descricao,b.codigo_busca AS item_codigo_busca ,a.unidade_id,a.lote_tipo,a.lote_spa,a.item_id,a.item_id_red,a.mov_id,a.oe,a.oe_seq,(b.plano_conta_id || ";" || b.plano_conta_numero) AS tmp_group,(b.plano_conta_id || ";" || b.codigo_busca) AS tmp_group_cb,60 AS ordem,b.producao_faixa_pliq_1 AS kg_cadastro_1,b.producao_faixa_pliq_2 AS kg_cadastro_2,'cadastro' AS kg_pc_tipo,0 AS kg_pc_1,0 AS kg_pc_2,0 AS valor_venda_un,'un' AS valor_venda_grandeza,0 AS valor_venda_grandeza_quant,(SELECT valor_custo_grandeza_quant FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza_quant,(SELECT valor_custo_grandeza FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza,(SELECT mc_minima FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS mc_minima,a.lote_sif_data,a.lote_sif,a.data_estoque FROM est_mov AS a, estoque_itens AS b, cfop_itens AS c , organograma AS d WHERE a.oe_tipo="tr" AND d.plano_conta_id=a.estoque_entrada_org AND d.plano_conta_id_red=a.estoque_entrada_org_red AND a.estoque_entrada_un=1001 AND d.plano_conta_numero = "3.05.01.01" AND a.item_id=67 AND b.plano_conta_id=a.item_id AND b.plano_conta_id_red=a.item_id_red AND b.codigo_busca LIKE "06%" AND c.plano_conta_id=a.cfop_id AND c.plano_conta_id_red=a.cfop_id_red AND c.plano_conta_id=16 AND c.plano_conta_numero = "0.995" AND a.lote_rendimento_data>="2014-11-13" AND a.lote_rendimento_data<"2014-11-17" AND a.rendimento='Y' AND a.lote_rendimento_numero>=3 AND a.lote_rendimento_numero<=3

this one takes 2:38 minutes

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	c	const	PRIMARY,NewIndex	PRIMARY	265	const,const	1	Using temporary
1	PRIMARY	b	range	PRIMARY,id,plano_conta_numero,cod_busca	cod_busca	265		53	Using index condition
1	PRIMARY	a	ref	cfop,item,transferencias,rendimento,estoque,giro	item	6	const,19_org.b.plano_conta_id_red	3316	Using where
1	PRIMARY	d	eq_ref	PRIMARY,NewIndex	PRIMARY	265	19_org.a.estoque_entrada_org,const	1	Using index condition; Using where
4	DEPENDENT SUBQUERY	unidade_itens_custo	eq_ref	PRIMARY	PRIMARY	24	19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red	1	Using index condition
3	DEPENDENT SUBQUERY	unidade_itens_custo	eq_ref	PRIMARY	PRIMARY	24	19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red	1	Using index condition
2	DEPENDENT SUBQUERY	unidade_itens_custo	eq_ref	PRIMARY	PRIMARY	24	19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red	1	Using index condition

Attachments

Activity

Ascending order - Click to sort in descending order

roberto spadim created issue - 2014-11-17 20:35

Sergei Golubchik made changes - 2014-11-18 14:54

Field	Original Value	New Value
Priority	Trivial [ 5 ]	Major [ 3 ]

Sergei Golubchik made changes - 2014-11-18 14:54

Due Date

2014-12-18

Elena Stepanova added a comment - 2014-11-18 15:34

Hi Roberto,

Is it reproducible on 10.0.14?

Elena Stepanova added a comment - 2014-11-18 15:34 Hi Roberto, Is it reproducible on 10.0.14?

roberto spadim added a comment - 2014-11-18 16:16 - edited

i will upgrade the database at weekend but probably yes, i checked with a replicate server (10.0.14) here, but not at production, next week i will check

roberto spadim added a comment - 2014-11-18 16:16 - edited i will upgrade the database at weekend but probably yes, i checked with a replicate server (10.0.14) here, but not at production, next week i will check

roberto spadim added a comment - 2014-11-18 16:17

i executed a analyze at production but this don't help, at weekend i will get a new position

roberto spadim added a comment - 2014-11-18 16:17 i executed a analyze at production but this don't help, at weekend i will get a new position

roberto spadim added a comment - 2014-11-19 16:39 - edited

i will update at weekend, if not work, i will check if patch of ~~MDEV-7118~~ make some good result, i'm not sure if it's running a loose scan or something like it instead of use the right index (it's a normal, not unique, index)

from what i look at patch (mdev-7118) - (just to don't loose the information)
=== modified file 'sql/opt_range.cc'
— a/sql/opt_range.cc 2014-10-28 21:33:31 +0000
+++ b/sql/opt_range.cc 2014-11-19 14:14:49 +0000
@@ -12799,11 +12799,11 @@ get_best_group_min_max(PARAM *param, SEL
uint cur_used_key_parts;

Check (B1) - if current index is covering. Exclude UNIQUE indexes, because
loose scan may still be chosen for them due to imperfect cost calculations.
+ Check (B1) - if current index is covering.
+ (was also: "Exclude UNIQUE indexes ..." but this was removed because
+ there are cases Loose Scan over a multi-part index is useful).
*/
if (!table->covering_keys.is_set(cur_index) ||
cur_index_info->flags & HA_NOSAME)
+ if (!table->covering_keys.is_set(cur_index))
goto next_index;

/*
@@ -12942,6 +12942,16 @@ get_best_group_min_max(PARAM *param, SEL
}

/*
+ Aplly a heuristic: there is no point to use loose index scan when we're
+ using the whole unique index.
+ */
+ if (cur_index_info->flags & HA_NOSAME &&
+ cur_group_key_parts == cur_index_info->user_defined_key_parts)
+

{ + goto next_index; + }

+
+ /*
Check (NGA1, NGA2) and extract a sequence of constants to be used as part
of all search keys.
*/

roberto spadim added a comment - 2014-11-19 16:39 - edited i will update at weekend, if not work, i will check if patch of MDEV-7118 make some good result, i'm not sure if it's running a loose scan or something like it instead of use the right index (it's a normal, not unique, index) from what i look at patch (mdev-7118) - (just to don't loose the information) === modified file 'sql/opt_range.cc' — a/sql/opt_range.cc 2014-10-28 21:33:31 +0000 +++ b/sql/opt_range.cc 2014-11-19 14:14:49 +0000 @@ -12799,11 +12799,11 @@ get_best_group_min_max(PARAM *param, SEL uint cur_used_key_parts; /* Check (B1) - if current index is covering. Exclude UNIQUE indexes, because loose scan may still be chosen for them due to imperfect cost calculations. + Check (B1) - if current index is covering. + (was also: "Exclude UNIQUE indexes ..." but this was removed because + there are cases Loose Scan over a multi-part index is useful). */ if (!table->covering_keys.is_set(cur_index) || cur_index_info->flags & HA_NOSAME) + if (!table->covering_keys.is_set(cur_index)) goto next_index; /* @@ -12942,6 +12942,16 @@ get_best_group_min_max(PARAM *param, SEL } /* + Aplly a heuristic: there is no point to use loose index scan when we're + using the whole unique index. + */ + if (cur_index_info->flags & HA_NOSAME && + cur_group_key_parts == cur_index_info->user_defined_key_parts) + { + goto next_index; + } + + /* Check (NGA1, NGA2) and extract a sequence of constants to be used as part of all search keys. */

roberto spadim added a comment - 2014-11-23 15:47

table analyzed... 10.0.14 ok i will take more tests at friday (many update/insert/delete) and check if optimizer don't get poor performace again

roberto spadim added a comment - 2014-11-23 15:47 table analyzed... 10.0.14 ok i will take more tests at friday (many update/insert/delete) and check if optimizer don't get poor performace again

roberto spadim added a comment - 2014-11-24 04:11

well, not many update/delete/insert and ...

/* Affected rows: 0 Registros encontrados: 0 Avisos: 0 Duração de 1 query: 00:08:13 */
8 minutes...
same wrong index

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	c	const	PRIMARY,NewIndex	PRIMARY	265	const,const	1	Using temporary
1	PRIMARY	b	range	PRIMARY,id,plano_conta_numero,cod_busca	cod_busca	265		49	Using index condition
1	PRIMARY	a	ref	cfop,item,transferencias,rendimento,estoque,giro	item	6	const,19_org.b.plano_conta_id_red	3347	Using where
1	PRIMARY	d	eq_ref	PRIMARY,NewIndex	PRIMARY	265	19_org.a.estoque_entrada_org,const	1	Using index condition; Using where
4	DEPENDENT SUBQUERY	unidade_itens_custo	eq_ref	PRIMARY	PRIMARY	24	19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red	1	Using index condition
3	DEPENDENT SUBQUERY	unidade_itens_custo	eq_ref	PRIMARY	PRIMARY	24	19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red	1	Using index condition
2	DEPENDENT SUBQUERY	unidade_itens_custo	eq_ref	PRIMARY	PRIMARY	24	19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red	1	Using index condition

roberto spadim added a comment - 2014-11-24 04:11 well, not many update/delete/insert and ... /* Affected rows: 0 Registros encontrados: 0 Avisos: 0 Duração de 1 query: 00:08:13 */ 8 minutes... same wrong index id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY c const PRIMARY,NewIndex PRIMARY 265 const,const 1 Using temporary 1 PRIMARY b range PRIMARY,id,plano_conta_numero,cod_busca cod_busca 265 49 Using index condition 1 PRIMARY a ref cfop,item,transferencias,rendimento,estoque,giro item 6 const,19_org.b.plano_conta_id_red 3347 Using where 1 PRIMARY d eq_ref PRIMARY,NewIndex PRIMARY 265 19_org.a.estoque_entrada_org,const 1 Using index condition; Using where 4 DEPENDENT SUBQUERY unidade_itens_custo eq_ref PRIMARY PRIMARY 24 19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red 1 Using index condition 3 DEPENDENT SUBQUERY unidade_itens_custo eq_ref PRIMARY PRIMARY 24 19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red 1 Using index condition 2 DEPENDENT SUBQUERY unidade_itens_custo eq_ref PRIMARY PRIMARY 24 19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red 1 Using index condition

roberto spadim added a comment - 2014-11-24 04:12

status (com_*)
updates = 100
inserts = 14
deletes = 21

roberto spadim added a comment - 2014-11-24 04:12 status (com_*) updates = 100 inserts = 14 deletes = 21

roberto spadim added a comment - 2014-11-24 04:16

some variables:
optimizer_prune_level=1
optimizer_search_depth=62
optimizer_selectivity_sampling_limit=100
optimizer_switch=index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on
optimizer_use_condition_selectivity=1
histogram_size=0
histogram_type=SINGLE_PREC_HB
use_stat_tables=NEVER

roberto spadim added a comment - 2014-11-24 04:16 some variables: optimizer_prune_level=1 optimizer_search_depth=62 optimizer_selectivity_sampling_limit=100 optimizer_switch=index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on optimizer_use_condition_selectivity=1 histogram_size=0 histogram_type=SINGLE_PREC_HB use_stat_tables=NEVER

roberto spadim added a comment - 2014-11-24 04:24

using "set @@optimizer_use_condition_selectivity=5;"
reduce from 8 minutes to 6 seconds:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	c	const	PRIMARY,NewIndex	PRIMARY	265	const,const	1	Using temporary
1	PRIMARY	b	range	PRIMARY,id,plano_conta_numero,cod_busca	cod_busca	265		49	Using index condition
1	PRIMARY	a	ref	cfop,item,transferencias,rendimento,estoque,giro	item	6	const,19_org.b.plano_conta_id_red	3347	Using where
1	PRIMARY	d	ALL	PRIMARY,NewIndex				7749	Using where; Using join buffer (flat, BNL join)
4	DEPENDENT SUBQUERY	unidade_itens_custo	eq_ref	PRIMARY	PRIMARY	24	19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red	1	Using index condition
3	DEPENDENT SUBQUERY	unidade_itens_custo	eq_ref	PRIMARY	PRIMARY	24	19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red	1	Using index condition
2	DEPENDENT SUBQUERY	unidade_itens_custo	eq_ref	PRIMARY	PRIMARY	24	19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red	1	Using index condition

roberto spadim added a comment - 2014-11-24 04:24 using "set @@optimizer_use_condition_selectivity=5;" reduce from 8 minutes to 6 seconds: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY c const PRIMARY,NewIndex PRIMARY 265 const,const 1 Using temporary 1 PRIMARY b range PRIMARY,id,plano_conta_numero,cod_busca cod_busca 265 49 Using index condition 1 PRIMARY a ref cfop,item,transferencias,rendimento,estoque,giro item 6 const,19_org.b.plano_conta_id_red 3347 Using where 1 PRIMARY d ALL PRIMARY,NewIndex 7749 Using where; Using join buffer (flat, BNL join) 4 DEPENDENT SUBQUERY unidade_itens_custo eq_ref PRIMARY PRIMARY 24 19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red 1 Using index condition 3 DEPENDENT SUBQUERY unidade_itens_custo eq_ref PRIMARY PRIMARY 24 19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red 1 Using index condition 2 DEPENDENT SUBQUERY unidade_itens_custo eq_ref PRIMARY PRIMARY 24 19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red 1 Using index condition

roberto spadim added a comment - 2014-11-24 04:28

using "set @@optimizer_use_condition_selectivity=5;"
and FORCE INDEX (rendimento)

reduce from 1,997 seconds to 0,252 seconds

roberto spadim added a comment - 2014-11-24 04:28 using "set @@optimizer_use_condition_selectivity=5;" and FORCE INDEX (rendimento) reduce from 1,997 seconds to 0,252 seconds

roberto spadim made changes - 2014-11-24 04:35

Comment

[ the same occurs to
set @@optimizer_use_condition_selectivity >= 2;
only set @@optimizer_use_condition_selectivity=1; works...

that's a consistent server crash
]

Elena Stepanova made changes - 2014-11-24 23:06

Due Date

2014-12-18

Elena Stepanova made changes - 2014-11-24 23:06

Affects Version/s

10.0 [ 16000 ]

Elena Stepanova made changes - 2014-11-24 23:07

Fix Version/s

10.0 [ 16000 ]

Elena Stepanova made changes - 2014-11-24 23:07

Assignee

Sergei Petrunia [ psergey ]

roberto spadim added a comment - 2014-11-25 01:40

well getting back to testing (upgraded to mariadb-10.0.15 from ~~MDEV-7182~~) i will check if optimizer is selecting the right index

roberto spadim added a comment - 2014-11-25 01:40 well getting back to testing (upgraded to mariadb-10.0.15 from MDEV-7182 ) i will check if optimizer is selecting the right index

roberto spadim added a comment - 2014-11-25 04:21

well, maybe we still have problems....

set @@optimizer_use_condition_selectivity = 5;

explain

SELECT sql_no_cache DISTINCT "" AS descricao,'+' AS add_sub,"PRODUTOS" AS tabela_preco,d.plano_conta_numero AS centro_numero,d.descricao AS centro_descricao,a.quant AS quant_real,a.pecas AS pecas_real,a.pliq AS pliq_real,a.pbruto AS pbruto_real,a.vliq AS vliq_real,a.vbruto AS vbruto_real,a.quant,a.pecas,a.pliq AS pliq,a.pbruto,a.vliq,a.vbruto,a.custo,a.lote_rendimento_data,a.lote_rendimento_numero,a.oe_tipo,c.plano_conta_numero AS cfop_numero,c.descricao AS cfop_descricao,b.plano_conta_numero AS item_numero,b.descricao AS item_descricao,b.codigo_busca AS item_codigo_busca ,a.unidade_id,a.lote_tipo,a.lote_spa,a.item_id,a.item_id_red,a.mov_id,a.oe,a.oe_seq,(b.plano_conta_id || ";" || b.plano_conta_numero) AS tmp_group,(b.plano_conta_id || ";" || b.codigo_busca) AS tmp_group_cb,60 AS ordem,b.producao_faixa_pliq_1 AS kg_cadastro_1,b.producao_faixa_pliq_2 AS kg_cadastro_2,'cadastro' AS kg_pc_tipo,0 AS kg_pc_1,0 AS kg_pc_2,0 AS valor_venda_un,'un' AS valor_venda_grandeza,0 AS valor_venda_grandeza_quant,(SELECT valor_custo_grandeza_quant FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza_quant,(SELECT valor_custo_grandeza FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza,(SELECT mc_minima FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS mc_minima,a.lote_sif_data,a.lote_sif,a.data_estoque

FROM est_mov AS a, estoque_itens AS b, cfop_itens AS c , organograma AS d

WHERE a.oe_tipo="tr" AND d.plano_conta_id=a.estoque_entrada_org AND d.plano_conta_id_red=a.estoque_entrada_org_red AND a.estoque_entrada_un=1001 AND d.plano_conta_numero = "3.05.01.01" AND a.item_id=67 AND b.plano_conta_id=a.item_id AND b.plano_conta_id_red=a.item_id_red AND b.codigo_busca LIKE "06%" AND c.plano_conta_id=a.cfop_id AND c.plano_conta_id_red=a.cfop_id_red AND c.plano_conta_id=16 AND c.plano_conta_numero = "0.995" AND a.lote_rendimento_data>="2014-11-13" AND a.lote_rendimento_data<"2014-11-17" AND a.rendimento='Y' AND a.lote_rendimento_numero>=3 AND a.lote_rendimento_numero<=3

8 minutes 04 seconds

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	c	const	PRIMARY,NewIndex	PRIMARY	265	const,const	1	Using temporary
1	PRIMARY	b	range	PRIMARY,id,plano_conta_numero,cod_busca	cod_busca	265		48	Using index condition
1	PRIMARY	a	ref	cfop,item,transferencias,rendimento,estoque,giro	item	6	const,19_org.b.plano_conta_id_red	3347	Using where
1	PRIMARY	d	ALL	PRIMARY,NewIndex				7749	Using where; Using join buffer (flat, BNL join)
4	DEPENDENT SUBQUERY	unidade_itens_custo	eq_ref	PRIMARY	PRIMARY	24	19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red	1	Using index condition
3	DEPENDENT SUBQUERY	unidade_itens_custo	eq_ref	PRIMARY	PRIMARY	24	19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red	1	Using index condition
2	DEPENDENT SUBQUERY	unidade_itens_custo	eq_ref	PRIMARY	PRIMARY	24	19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red	1	Using index condition

—
set @@optimizer_use_condition_selectivity = 1;
19 seconds first time, 6 seconds 2nd,3rd,4...10 time
—
set @@optimizer_use_condition_selectivity = 5;
8 minutes 04 seconds first time, 6 seconds 2nd,3rd,4...10 time

—
using FORCE INDEX(rendimento)
set @@optimizer_use_condition_selectivity = 5;
2.339 seconds first time, 0,203 2nd,3rd,4...10 time
—
set @@optimizer_use_condition_selectivity = 1;
0,515 seconds first time, 0,17 2nd,3rd,4...10 time

====
for the first time we have a big time, ok no buffers/cache, seconds and others have buffer/cache and execute faster

explain with FORCE INDEX:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	c	const	PRIMARY,NewIndex	PRIMARY	265	const,const	1	Using temporary
1	PRIMARY	a	range	rendimento	rendimento	4		238077	Using where
1	PRIMARY	d	eq_ref	PRIMARY,NewIndex	PRIMARY	265	19_org.a.estoque_entrada_org,const	1	Using index condition; Using where
1	PRIMARY	b	eq_ref	PRIMARY,id,plano_conta_numero,cod_busca	id	16	const,19_org.a.item_id_red	1	Using index condition; Using where
4	DEPENDENT SUBQUERY	unidade_itens_custo	eq_ref	PRIMARY	PRIMARY	24	19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red	1	Using index condition
3	DEPENDENT SUBQUERY	unidade_itens_custo	eq_ref	PRIMARY	PRIMARY	24	19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red	1	Using index condition
2	DEPENDENT SUBQUERY	unidade_itens_custo	eq_ref	PRIMARY	PRIMARY	24	19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red	1	Using index condition

—
maybe something is prefering a more complex search instead of a single index search

roberto spadim added a comment - 2014-11-25 04:21 well, maybe we still have problems.... set @@optimizer_use_condition_selectivity = 5; explain SELECT sql_no_cache DISTINCT "" AS descricao,'+' AS add_sub,"PRODUTOS" AS tabela_preco,d.plano_conta_numero AS centro_numero,d.descricao AS centro_descricao,a.quant AS quant_real,a.pecas AS pecas_real,a.pliq AS pliq_real,a.pbruto AS pbruto_real,a.vliq AS vliq_real,a.vbruto AS vbruto_real,a.quant,a.pecas,a.pliq AS pliq,a.pbruto,a.vliq,a.vbruto,a.custo,a.lote_rendimento_data,a.lote_rendimento_numero,a.oe_tipo,c.plano_conta_numero AS cfop_numero,c.descricao AS cfop_descricao,b.plano_conta_numero AS item_numero,b.descricao AS item_descricao,b.codigo_busca AS item_codigo_busca ,a.unidade_id,a.lote_tipo,a.lote_spa,a.item_id,a.item_id_red,a.mov_id,a.oe,a.oe_seq,(b.plano_conta_id || ";" || b.plano_conta_numero) AS tmp_group,(b.plano_conta_id || ";" || b.codigo_busca) AS tmp_group_cb,60 AS ordem,b.producao_faixa_pliq_1 AS kg_cadastro_1,b.producao_faixa_pliq_2 AS kg_cadastro_2,'cadastro' AS kg_pc_tipo,0 AS kg_pc_1,0 AS kg_pc_2,0 AS valor_venda_un,'un' AS valor_venda_grandeza,0 AS valor_venda_grandeza_quant,(SELECT valor_custo_grandeza_quant FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza_quant,(SELECT valor_custo_grandeza FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza,(SELECT mc_minima FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS mc_minima,a.lote_sif_data,a.lote_sif,a.data_estoque FROM est_mov AS a, estoque_itens AS b, cfop_itens AS c , organograma AS d WHERE a.oe_tipo="tr" AND d.plano_conta_id=a.estoque_entrada_org AND d.plano_conta_id_red=a.estoque_entrada_org_red AND a.estoque_entrada_un=1001 AND d.plano_conta_numero = "3.05.01.01" AND a.item_id=67 AND b.plano_conta_id=a.item_id AND b.plano_conta_id_red=a.item_id_red AND b.codigo_busca LIKE "06%" AND c.plano_conta_id=a.cfop_id AND c.plano_conta_id_red=a.cfop_id_red AND c.plano_conta_id=16 AND c.plano_conta_numero = "0.995" AND a.lote_rendimento_data>="2014-11-13" AND a.lote_rendimento_data<"2014-11-17" AND a.rendimento='Y' AND a.lote_rendimento_numero>=3 AND a.lote_rendimento_numero<=3 8 minutes 04 seconds id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY c const PRIMARY,NewIndex PRIMARY 265 const,const 1 Using temporary 1 PRIMARY b range PRIMARY,id,plano_conta_numero,cod_busca cod_busca 265 48 Using index condition 1 PRIMARY a ref cfop,item,transferencias,rendimento,estoque,giro item 6 const,19_org.b.plano_conta_id_red 3347 Using where 1 PRIMARY d ALL PRIMARY,NewIndex 7749 Using where; Using join buffer (flat, BNL join) 4 DEPENDENT SUBQUERY unidade_itens_custo eq_ref PRIMARY PRIMARY 24 19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red 1 Using index condition 3 DEPENDENT SUBQUERY unidade_itens_custo eq_ref PRIMARY PRIMARY 24 19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red 1 Using index condition 2 DEPENDENT SUBQUERY unidade_itens_custo eq_ref PRIMARY PRIMARY 24 19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red 1 Using index condition — set @@optimizer_use_condition_selectivity = 1; 19 seconds first time, 6 seconds 2nd,3rd,4...10 time — set @@optimizer_use_condition_selectivity = 5; 8 minutes 04 seconds first time, 6 seconds 2nd,3rd,4...10 time — using FORCE INDEX(rendimento) set @@optimizer_use_condition_selectivity = 5; 2.339 seconds first time, 0,203 2nd,3rd,4...10 time — set @@optimizer_use_condition_selectivity = 1; 0,515 seconds first time, 0,17 2nd,3rd,4...10 time ==== for the first time we have a big time, ok no buffers/cache, seconds and others have buffer/cache and execute faster explain with FORCE INDEX: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY c const PRIMARY,NewIndex PRIMARY 265 const,const 1 Using temporary 1 PRIMARY a range rendimento rendimento 4 238077 Using where 1 PRIMARY d eq_ref PRIMARY,NewIndex PRIMARY 265 19_org.a.estoque_entrada_org,const 1 Using index condition; Using where 1 PRIMARY b eq_ref PRIMARY,id,plano_conta_numero,cod_busca id 16 const,19_org.a.item_id_red 1 Using index condition; Using where 4 DEPENDENT SUBQUERY unidade_itens_custo eq_ref PRIMARY PRIMARY 24 19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red 1 Using index condition 3 DEPENDENT SUBQUERY unidade_itens_custo eq_ref PRIMARY PRIMARY 24 19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red 1 Using index condition 2 DEPENDENT SUBQUERY unidade_itens_custo eq_ref PRIMARY PRIMARY 24 19_org.a.unidade_id,19_org.a.item_id,19_org.a.item_id_red 1 Using index condition — maybe something is prefering a more complex search instead of a single index search

roberto spadim added a comment - 2014-11-25 04:21

any space to upload private data available?

roberto spadim added a comment - 2014-11-25 04:21 any space to upload private data available?

Sergei Petrunia added a comment - 2014-11-25 12:01

it's ftp://ftp.askmonty.org, log in as anonymous and it's possible to upload the data which will not be publicly visible. Please mention the file name here so we can find it

Sergei Petrunia added a comment - 2014-11-25 12:01 it's ftp://ftp.askmonty.org , log in as anonymous and it's possible to upload the data which will not be publicly visible. Please mention the file name here so we can find it

roberto spadim added a comment - 2014-11-26 04:42

i start dump now, with some hours i will upload as mdev-7125.sql.tgz
i changed table est_mov to est_mov_myisam (from replica database)

any news i will comment here again
thanks

roberto spadim added a comment - 2014-11-26 04:42 i start dump now, with some hours i will upload as mdev-7125.sql.tgz i changed table est_mov to est_mov_myisam (from replica database) any news i will comment here again thanks

roberto spadim added a comment - 2014-11-26 04:43

should i use:
ftp://ftp.askmonty.org/private/
or
ftp://ftp.askmonty.org/public/
or
ftp://ftp.askmonty.org/secret/
?

roberto spadim added a comment - 2014-11-26 04:43 should i use: ftp://ftp.askmonty.org/private/ or ftp://ftp.askmonty.org/public/ or ftp://ftp.askmonty.org/secret/ ?

roberto spadim added a comment - 2014-11-26 05:28

tested with private, i'm compatcing and sending file

roberto spadim added a comment - 2014-11-26 05:28 tested with private, i'm compatcing and sending file

Sergei Petrunia made changes - 2014-11-26 22:12

Assignee

Sergei Petrunia [ psergey ]

Elena Stepanova [ elenst ]

Elena Stepanova made changes - 2014-12-01 12:49

Status

Open [ 1 ]

In Progress [ 3 ]

Elena Stepanova added a comment - 2014-12-01 12:56 - edited

Experimenting on 10.0.14 for starters.
My results on the initial query for further comparison (I only count "warm" results):

Forced index: 0.22 sec

SELECT DISTINCT "GOOD" AS descricao,'+' AS add_sub,"PRODUTOS" AS tabela_preco,d.plano_conta_numero AS centro_numero,d.descricao AS centro_descricao,a.quant AS quant_real,a.pecas AS pecas_real,a.pliq AS pliq_real,a.pbruto AS pbruto_real,a.vliq AS vliq_real,a.vbruto AS vbruto_real,a.quant,a.pecas,a.pliq AS pliq,a.pbruto,a.vliq,a.vbruto,a.custo,a.lote_rendimento_data,a.lote_rendimento_numero,a.oe_tipo,c.plano_conta_numero AS cfop_numero,c.descricao AS cfop_descricao,b.plano_conta_numero AS item_numero,b.descricao AS item_descricao,b.codigo_busca AS item_codigo_busca ,a.unidade_id,a.lote_tipo,a.lote_spa,a.item_id,a.item_id_red,a.mov_id,a.oe,a.oe_seq,(b.plano_conta_id || ";" || b.plano_conta_numero) AS tmp_group,(b.plano_conta_id || ";" || b.codigo_busca) AS tmp_group_cb,60 AS ordem,b.producao_faixa_pliq_1 AS kg_cadastro_1,b.producao_faixa_pliq_2 AS kg_cadastro_2,'cadastro' AS kg_pc_tipo,0 AS kg_pc_1,0 AS kg_pc_2,0 AS valor_venda_un,'un' AS valor_venda_grandeza,0 AS valor_venda_grandeza_quant,(SELECT valor_custo_grandeza_quant FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza_quant,(SELECT valor_custo_grandeza FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza,(SELECT mc_minima FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS mc_minima,a.lote_sif_data,a.lote_sif,a.data_estoque FROM est_mov_myisam a force index(rendimento), estoque_itens AS b, cfop_itens AS c , organograma AS d WHERE a.oe_tipo="tr" AND d.plano_conta_id=a.estoque_entrada_org AND d.plano_conta_id_red=a.estoque_entrada_org_red AND a.estoque_entrada_un=1001 AND d.plano_conta_numero = "3.05.01.01" AND a.item_id=67 AND b.plano_conta_id=a.item_id AND b.plano_conta_id_red=a.item_id_red AND b.codigo_busca LIKE "06%" AND c.plano_conta_id=a.cfop_id AND c.plano_conta_id_red=a.cfop_id_red AND c.plano_conta_id=16 AND c.plano_conta_numero = "0.995" AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y' AND a.lote_rendimento_numero>=3 AND a.lote_rendimento_numero<=3;

+------+--------------------+---------------------+--------+-----------------------------------------+------------+---------+-----------------------------------------------------+-------+----------+------------------------------------+

| id   | select_type        | table               | type   | possible_keys                           | key        | key_len | ref                                                 | rows  | filtered | Extra                              |

+------+--------------------+---------------------+--------+-----------------------------------------+------------+---------+-----------------------------------------------------+-------+----------+------------------------------------+

|    1 | PRIMARY            | c                   | const  | PRIMARY,NewIndex                        | PRIMARY    | 265     | const,const                                         |     1 |   100.00 | Using temporary                    |

|    1 | PRIMARY            | a                   | range  | rendimento                              | rendimento | 4       | NULL                                                | 99850 |    75.00 | Using where                        |

|    1 | PRIMARY            | d                   | eq_ref | PRIMARY,NewIndex                        | PRIMARY    | 265     | test.a.estoque_entrada_org,const                    |     1 |   100.00 | Using index condition; Using where |

|    1 | PRIMARY            | b                   | eq_ref | PRIMARY,id,plano_conta_numero,cod_busca | id         | 16      | const,test.a.item_id_red                            |     1 |   100.00 | Using index condition; Using where |

|    4 | DEPENDENT SUBQUERY | unidade_itens_custo | eq_ref | PRIMARY                                 | PRIMARY    | 24      | test.a.unidade_id,test.a.item_id,test.a.item_id_red |     1 |   100.00 | Using index condition              |

|    3 | DEPENDENT SUBQUERY | unidade_itens_custo | eq_ref | PRIMARY                                 | PRIMARY    | 24      | test.a.unidade_id,test.a.item_id,test.a.item_id_red |     1 |   100.00 | Using index condition              |

|    2 | DEPENDENT SUBQUERY | unidade_itens_custo | eq_ref | PRIMARY                                 | PRIMARY    | 24      | test.a.unidade_id,test.a.item_id,test.a.item_id_red |     1 |   100.00 | Using index condition              |

+------+--------------------+---------------------+--------+-----------------------------------------+------------+---------+-----------------------------------------------------+-------+----------+------------------------------------+

No index: 3.28 sec

SELECT DISTINCT "BAD" AS descricao,'+' AS add_sub,"PRODUTOS" AS tabela_preco,d.plano_conta_numero AS centro_numero,d.descricao AS centro_descricao,a.quant AS quant_real,a.pecas AS pecas_real,a.pliq AS pliq_real,a.pbruto AS pbruto_real,a.vliq AS vliq_real,a.vbruto AS vbruto_real,a.quant,a.pecas,a.pliq AS pliq,a.pbruto,a.vliq,a.vbruto,a.custo,a.lote_rendimento_data,a.lote_rendimento_numero,a.oe_tipo,c.plano_conta_numero AS cfop_numero,c.descricao AS cfop_descricao,b.plano_conta_numero AS item_numero,b.descricao AS item_descricao,b.codigo_busca AS item_codigo_busca ,a.unidade_id,a.lote_tipo,a.lote_spa,a.item_id,a.item_id_red,a.mov_id,a.oe,a.oe_seq,(b.plano_conta_id || ";" || b.plano_conta_numero) AS tmp_group,(b.plano_conta_id || ";" || b.codigo_busca) AS tmp_group_cb,60 AS ordem,b.producao_faixa_pliq_1 AS kg_cadastro_1,b.producao_faixa_pliq_2 AS kg_cadastro_2,'cadastro' AS kg_pc_tipo,0 AS kg_pc_1,0 AS kg_pc_2,0 AS valor_venda_un,'un' AS valor_venda_grandeza,0 AS valor_venda_grandeza_quant,(SELECT valor_custo_grandeza_quant FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza_quant,(SELECT valor_custo_grandeza FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza,(SELECT mc_minima FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS mc_minima,a.lote_sif_data,a.lote_sif,a.data_estoque FROM est_mov_myisam a, estoque_itens AS b, cfop_itens AS c , organograma AS d  WHERE a.oe_tipo="tr" AND d.plano_conta_id=a.estoque_entrada_org AND d.plano_conta_id_red=a.estoque_entrada_org_red AND a.estoque_entrada_un=1001 AND d.plano_conta_numero = "3.05.01.01" AND a.item_id=67 AND b.plano_conta_id=a.item_id AND b.plano_conta_id_red=a.item_id_red AND b.codigo_busca LIKE "06%" AND c.plano_conta_id=a.cfop_id AND c.plano_conta_id_red=a.cfop_id_red AND c.plano_conta_id=16 AND c.plano_conta_numero = "0.995" AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y' AND a.lote_rendimento_numero>=3 AND a.lote_rendimento_numero<=3;

+------+--------------------+---------------------+--------+--------------------------------------------------+-----------+---------+-----------------------------------------------------+------+----------+------------------------------------+

| id   | select_type        | table               | type   | possible_keys                                    | key       | key_len | ref                                                 | rows | filtered | Extra                              |

+------+--------------------+---------------------+--------+--------------------------------------------------+-----------+---------+-----------------------------------------------------+------+----------+------------------------------------+

|    1 | PRIMARY            | c                   | const  | PRIMARY,NewIndex                                 | PRIMARY   | 265     | const,const                                         |    1 |   100.00 | Using temporary                    |

|    1 | PRIMARY            | b                   | range  | PRIMARY,id,plano_conta_numero,cod_busca          | cod_busca | 265     | NULL                                                |   42 |    76.19 | Using index condition              |

|    1 | PRIMARY            | a                   | ref    | cfop,item,transferencias,rendimento,estoque,giro | item      | 6       | const,test.b.plano_conta_id_red                     | 3318 |   100.00 | Using where                        |

|    1 | PRIMARY            | d                   | eq_ref | PRIMARY,NewIndex                                 | PRIMARY   | 265     | test.a.estoque_entrada_org,const                    |    1 |   100.00 | Using index condition; Using where |

|    4 | DEPENDENT SUBQUERY | unidade_itens_custo | eq_ref | PRIMARY                                          | PRIMARY   | 24      | test.a.unidade_id,test.a.item_id,test.a.item_id_red |    1 |   100.00 | Using index condition              |

|    3 | DEPENDENT SUBQUERY | unidade_itens_custo | eq_ref | PRIMARY                                          | PRIMARY   | 24      | test.a.unidade_id,test.a.item_id,test.a.item_id_red |    1 |   100.00 | Using index condition              |

|    2 | DEPENDENT SUBQUERY | unidade_itens_custo | eq_ref | PRIMARY                                          | PRIMARY   | 24      | test.a.unidade_id,test.a.item_id,test.a.item_id_red |    1 |   100.00 | Using index condition              |

+------+--------------------+---------------------+--------+--------------------------------------------------+-----------+---------+-----------------------------------------------------+------+----------+------------------------------------+

Elena Stepanova added a comment - 2014-12-01 12:56 - edited Experimenting on 10.0.14 for starters. My results on the initial query for further comparison (I only count "warm" results): Forced index: 0.22 sec SELECT DISTINCT "GOOD" AS descricao, '+' AS add_sub, "PRODUTOS" AS tabela_preco,d.plano_conta_numero AS centro_numero,d.descricao AS centro_descricao,a.quant AS quant_real,a.pecas AS pecas_real,a.pliq AS pliq_real,a.pbruto AS pbruto_real,a.vliq AS vliq_real,a.vbruto AS vbruto_real,a.quant,a.pecas,a.pliq AS pliq,a.pbruto,a.vliq,a.vbruto,a.custo,a.lote_rendimento_data,a.lote_rendimento_numero,a.oe_tipo,c.plano_conta_numero AS cfop_numero,c.descricao AS cfop_descricao,b.plano_conta_numero AS item_numero,b.descricao AS item_descricao,b.codigo_busca AS item_codigo_busca ,a.unidade_id,a.lote_tipo,a.lote_spa,a.item_id,a.item_id_red,a.mov_id,a.oe,a.oe_seq,(b.plano_conta_id || ";" || b.plano_conta_numero) AS tmp_group,(b.plano_conta_id || ";" || b.codigo_busca) AS tmp_group_cb,60 AS ordem,b.producao_faixa_pliq_1 AS kg_cadastro_1,b.producao_faixa_pliq_2 AS kg_cadastro_2, 'cadastro' AS kg_pc_tipo,0 AS kg_pc_1,0 AS kg_pc_2,0 AS valor_venda_un, 'un' AS valor_venda_grandeza,0 AS valor_venda_grandeza_quant,( SELECT valor_custo_grandeza_quant FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza_quant,( SELECT valor_custo_grandeza FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza,( SELECT mc_minima FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS mc_minima,a.lote_sif_data,a.lote_sif,a.data_estoque FROM est_mov_myisam a force index (rendimento), estoque_itens AS b, cfop_itens AS c , organograma AS d WHERE a.oe_tipo= "tr" AND d.plano_conta_id=a.estoque_entrada_org AND d.plano_conta_id_red=a.estoque_entrada_org_red AND a.estoque_entrada_un=1001 AND d.plano_conta_numero = "3.05.01.01" AND a.item_id=67 AND b.plano_conta_id=a.item_id AND b.plano_conta_id_red=a.item_id_red AND b.codigo_busca LIKE "06%" AND c.plano_conta_id=a.cfop_id AND c.plano_conta_id_red=a.cfop_id_red AND c.plano_conta_id=16 AND c.plano_conta_numero = "0.995" AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' AND a.lote_rendimento_numero>=3 AND a.lote_rendimento_numero<=3; + ------+--------------------+---------------------+--------+-----------------------------------------+------------+---------+-----------------------------------------------------+-------+----------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ------+--------------------+---------------------+--------+-----------------------------------------+------------+---------+-----------------------------------------------------+-------+----------+------------------------------------+ | 1 | PRIMARY | c | const | PRIMARY ,NewIndex | PRIMARY | 265 | const,const | 1 | 100.00 | Using temporary | | 1 | PRIMARY | a | range | rendimento | rendimento | 4 | NULL | 99850 | 75.00 | Using where | | 1 | PRIMARY | d | eq_ref | PRIMARY ,NewIndex | PRIMARY | 265 | test.a.estoque_entrada_org,const | 1 | 100.00 | Using index condition; Using where | | 1 | PRIMARY | b | eq_ref | PRIMARY ,id,plano_conta_numero,cod_busca | id | 16 | const,test.a.item_id_red | 1 | 100.00 | Using index condition; Using where | | 4 | DEPENDENT SUBQUERY | unidade_itens_custo | eq_ref | PRIMARY | PRIMARY | 24 | test.a.unidade_id,test.a.item_id,test.a.item_id_red | 1 | 100.00 | Using index condition | | 3 | DEPENDENT SUBQUERY | unidade_itens_custo | eq_ref | PRIMARY | PRIMARY | 24 | test.a.unidade_id,test.a.item_id,test.a.item_id_red | 1 | 100.00 | Using index condition | | 2 | DEPENDENT SUBQUERY | unidade_itens_custo | eq_ref | PRIMARY | PRIMARY | 24 | test.a.unidade_id,test.a.item_id,test.a.item_id_red | 1 | 100.00 | Using index condition | + ------+--------------------+---------------------+--------+-----------------------------------------+------------+---------+-----------------------------------------------------+-------+----------+------------------------------------+ No index: 3.28 sec SELECT DISTINCT "BAD" AS descricao, '+' AS add_sub, "PRODUTOS" AS tabela_preco,d.plano_conta_numero AS centro_numero,d.descricao AS centro_descricao,a.quant AS quant_real,a.pecas AS pecas_real,a.pliq AS pliq_real,a.pbruto AS pbruto_real,a.vliq AS vliq_real,a.vbruto AS vbruto_real,a.quant,a.pecas,a.pliq AS pliq,a.pbruto,a.vliq,a.vbruto,a.custo,a.lote_rendimento_data,a.lote_rendimento_numero,a.oe_tipo,c.plano_conta_numero AS cfop_numero,c.descricao AS cfop_descricao,b.plano_conta_numero AS item_numero,b.descricao AS item_descricao,b.codigo_busca AS item_codigo_busca ,a.unidade_id,a.lote_tipo,a.lote_spa,a.item_id,a.item_id_red,a.mov_id,a.oe,a.oe_seq,(b.plano_conta_id || ";" || b.plano_conta_numero) AS tmp_group,(b.plano_conta_id || ";" || b.codigo_busca) AS tmp_group_cb,60 AS ordem,b.producao_faixa_pliq_1 AS kg_cadastro_1,b.producao_faixa_pliq_2 AS kg_cadastro_2, 'cadastro' AS kg_pc_tipo,0 AS kg_pc_1,0 AS kg_pc_2,0 AS valor_venda_un, 'un' AS valor_venda_grandeza,0 AS valor_venda_grandeza_quant,( SELECT valor_custo_grandeza_quant FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza_quant,( SELECT valor_custo_grandeza FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS valor_custo_grandeza,( SELECT mc_minima FROM unidade_itens_custo WHERE unidade_id=a.unidade_id AND item_id=a.item_id AND item_id_red=a.item_id_red) AS mc_minima,a.lote_sif_data,a.lote_sif,a.data_estoque FROM est_mov_myisam a, estoque_itens AS b, cfop_itens AS c , organograma AS d WHERE a.oe_tipo= "tr" AND d.plano_conta_id=a.estoque_entrada_org AND d.plano_conta_id_red=a.estoque_entrada_org_red AND a.estoque_entrada_un=1001 AND d.plano_conta_numero = "3.05.01.01" AND a.item_id=67 AND b.plano_conta_id=a.item_id AND b.plano_conta_id_red=a.item_id_red AND b.codigo_busca LIKE "06%" AND c.plano_conta_id=a.cfop_id AND c.plano_conta_id_red=a.cfop_id_red AND c.plano_conta_id=16 AND c.plano_conta_numero = "0.995" AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' AND a.lote_rendimento_numero>=3 AND a.lote_rendimento_numero<=3; + ------+--------------------+---------------------+--------+--------------------------------------------------+-----------+---------+-----------------------------------------------------+------+----------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ------+--------------------+---------------------+--------+--------------------------------------------------+-----------+---------+-----------------------------------------------------+------+----------+------------------------------------+ | 1 | PRIMARY | c | const | PRIMARY ,NewIndex | PRIMARY | 265 | const,const | 1 | 100.00 | Using temporary | | 1 | PRIMARY | b | range | PRIMARY ,id,plano_conta_numero,cod_busca | cod_busca | 265 | NULL | 42 | 76.19 | Using index condition | | 1 | PRIMARY | a | ref | cfop,item,transferencias,rendimento,estoque,giro | item | 6 | const,test.b.plano_conta_id_red | 3318 | 100.00 | Using where | | 1 | PRIMARY | d | eq_ref | PRIMARY ,NewIndex | PRIMARY | 265 | test.a.estoque_entrada_org,const | 1 | 100.00 | Using index condition; Using where | | 4 | DEPENDENT SUBQUERY | unidade_itens_custo | eq_ref | PRIMARY | PRIMARY | 24 | test.a.unidade_id,test.a.item_id,test.a.item_id_red | 1 | 100.00 | Using index condition | | 3 | DEPENDENT SUBQUERY | unidade_itens_custo | eq_ref | PRIMARY | PRIMARY | 24 | test.a.unidade_id,test.a.item_id,test.a.item_id_red | 1 | 100.00 | Using index condition | | 2 | DEPENDENT SUBQUERY | unidade_itens_custo | eq_ref | PRIMARY | PRIMARY | 24 | test.a.unidade_id,test.a.item_id,test.a.item_id_red | 1 | 100.00 | Using index condition | + ------+--------------------+---------------------+--------+--------------------------------------------------+-----------+---------+-----------------------------------------------------+------+----------+------------------------------------+

Elena Stepanova added a comment - 2014-12-01 13:00 - edited

Still on 10.0.14.
Simplified query (it takes much longer on the same data, which means we can possibly reduce the data and still see the performance difference):

Forced index: 1 min 20 sec

SELECT COUNT(*) AS GOOD FROM est_mov_myisam a force index(rendimento), estoque_itens AS b, cfop_itens AS c

WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y' ;

+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------------------------------------+

| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows  | filtered | Extra                                                               |

+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------------------------------------+

|    1 | SIMPLE      | a     | range | rendimento    | rendimento | 4       | NULL | 99850 |    75.00 | Using where                                                         |

|    1 | SIMPLE      | c     | index | NULL          | NewIndex   | 16      | NULL |   280 |   100.00 | Using where; Using index; Using join buffer (flat, BNL join)        |

|    1 | SIMPLE      | b     | index | NULL          | id         | 16      | NULL |  8818 |   100.00 | Using where; Using index; Using join buffer (incremental, BNL join) |

+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------------------------------------+

No index: interrupted after 26 min of execution

SELECT COUNT(*) AS BAD FROM est_mov_myisam a, estoque_itens AS b, cfop_itens AS c

WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y' ;

+------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+

| id   | select_type | table | type  | possible_keys                       | key      | key_len | ref                             | rows | filtered | Extra                                                        |

+------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+

|    1 | SIMPLE      | b     | index | NULL                                | id       | 16      | NULL                            | 8818 |   100.00 | Using index                                                  |

|    1 | SIMPLE      | a     | ref   | item,transferencias,rendimento,giro | item     | 6       | const,test.b.plano_conta_id_red | 3318 |   100.00 | Using where                                                  |

|    1 | SIMPLE      | c     | index | NULL                                | NewIndex | 16      | NULL                            |  280 |   100.00 | Using where; Using index; Using join buffer (flat, BNL join) |

+------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+

Elena Stepanova added a comment - 2014-12-01 13:00 - edited Still on 10.0.14. Simplified query (it takes much longer on the same data, which means we can possibly reduce the data and still see the performance difference): Forced index: 1 min 20 sec SELECT COUNT (*) AS GOOD FROM est_mov_myisam a force index (rendimento), estoque_itens AS b, cfop_itens AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' ; + ------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------------------------------------+ | 1 | SIMPLE | a | range | rendimento | rendimento | 4 | NULL | 99850 | 75.00 | Using where | | 1 | SIMPLE | c | index | NULL | NewIndex | 16 | NULL | 280 | 100.00 | Using where ; Using index ; Using join buffer (flat, BNL join ) | | 1 | SIMPLE | b | index | NULL | id | 16 | NULL | 8818 | 100.00 | Using where ; Using index ; Using join buffer (incremental, BNL join ) | + ------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------------------------------------+ No index: interrupted after 26 min of execution SELECT COUNT (*) AS BAD FROM est_mov_myisam a, estoque_itens AS b, cfop_itens AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' ; + ------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+ | 1 | SIMPLE | b | index | NULL | id | 16 | NULL | 8818 | 100.00 | Using index | | 1 | SIMPLE | a | ref | item,transferencias,rendimento,giro | item | 6 | const,test.b.plano_conta_id_red | 3318 | 100.00 | Using where | | 1 | SIMPLE | c | index | NULL | NewIndex | 16 | NULL | 280 | 100.00 | Using where ; Using index ; Using join buffer (flat, BNL join ) | + ------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+

Elena Stepanova added a comment - 2014-12-01 13:56 - edited

The number of rows in tables 'b' and 'c' is not important for reproducing the difference, only for the absolute execution time.
Below `t2` is the same as `estoque_itens`, and `t3` is the same as `cfop_itens`, only both `t2` and `t3` have 2 rows each:

Forced index: 0.28 sec

SELECT COUNT(*) AS GOOD FROM est_mov_myisam a force index(rendimento), t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y';

+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------------------------------------+

| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows  | filtered | Extra                                                               |

+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------------------------------------+

|    1 | SIMPLE      | a     | range | rendimento    | rendimento | 4       | NULL | 99850 |    75.00 | Using where                                                         |

|    1 | SIMPLE      | c     | index | NULL          | NewIndex   | 16      | NULL |     2 |   100.00 | Using where; Using index; Using join buffer (flat, BNL join)        |

|    1 | SIMPLE      | b     | index | NULL          | id         | 16      | NULL |     2 |   100.00 | Using where; Using index; Using join buffer (incremental, BNL join) |

+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------------------------------------+

No index: 2.68 sec

SELECT COUNT(*) AS BAD FROM est_mov_myisam a, t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y';

+------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+

| id   | select_type | table | type  | possible_keys                       | key      | key_len | ref                             | rows | filtered | Extra                                                        |

+------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+

|    1 | SIMPLE      | b     | index | NULL                                | id       | 16      | NULL                            |    2 |   100.00 | Using index                                                  |

|    1 | SIMPLE      | a     | ref   | item,transferencias,rendimento,giro | item     | 6       | const,test.b.plano_conta_id_red | 3318 |   100.00 | Using where                                                  |

|    1 | SIMPLE      | c     | index | NULL                                | NewIndex | 16      | NULL                            |    2 |   100.00 | Using where; Using index; Using join buffer (flat, BNL join) |

+------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+

Elena Stepanova added a comment - 2014-12-01 13:56 - edited The number of rows in tables 'b' and 'c' is not important for reproducing the difference, only for the absolute execution time. Below `t2` is the same as `estoque_itens`, and `t3` is the same as `cfop_itens`, only both `t2` and `t3` have 2 rows each: Forced index: 0.28 sec SELECT COUNT (*) AS GOOD FROM est_mov_myisam a force index (rendimento), t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' ; + ------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------------------------------------+ | 1 | SIMPLE | a | range | rendimento | rendimento | 4 | NULL | 99850 | 75.00 | Using where | | 1 | SIMPLE | c | index | NULL | NewIndex | 16 | NULL | 2 | 100.00 | Using where ; Using index ; Using join buffer (flat, BNL join ) | | 1 | SIMPLE | b | index | NULL | id | 16 | NULL | 2 | 100.00 | Using where ; Using index ; Using join buffer (incremental, BNL join ) | + ------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------------------------------------------+ No index: 2.68 sec SELECT COUNT (*) AS BAD FROM est_mov_myisam a, t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' ; + ------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+ | 1 | SIMPLE | b | index | NULL | id | 16 | NULL | 2 | 100.00 | Using index | | 1 | SIMPLE | a | ref | item,transferencias,rendimento,giro | item | 6 | const,test.b.plano_conta_id_red | 3318 | 100.00 | Using where | | 1 | SIMPLE | c | index | NULL | NewIndex | 16 | NULL | 2 | 100.00 | Using where ; Using index ; Using join buffer (flat, BNL join ) | + ------+-------------+-------+-------+-------------------------------------+----------+---------+---------------------------------+------+----------+--------------------------------------------------------------+

Elena Stepanova added a comment - 2014-12-01 14:05

Partitioning on est_mov_myisam is not important. Below `t1` is the same as `est_mov_myisam` (which is, btw, an Aria table!), but without partitioning.

Forced index: 0.21 sec

SELECT COUNT(*) AS GOOD FROM t1 a force index(rendimento), t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y';

No index: 2.87 sec

SELECT COUNT(*) AS BAD FROM t1 a, t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y';

Elena Stepanova added a comment - 2014-12-01 14:05 Partitioning on est_mov_myisam is not important. Below `t1` is the same as `est_mov_myisam` (which is, btw, an Aria table!), but without partitioning. Forced index: 0.21 sec SELECT COUNT (*) AS GOOD FROM t1 a force index (rendimento), t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' ; No index: 2.87 sec SELECT COUNT (*) AS BAD FROM t1 a, t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' ;

roberto spadim added a comment - 2014-12-01 14:11 - edited

yeap data engine didn't change optimizer
i got as aria/myisam from a replicated server
i tryed with innodb from master and same problem, i think it's related to some optimizer cost function selecting the "item" index instead of "rendimento" index, i don't know if optimizer prefer primary key columns is it possible?

i will include a new mdev to explain better why optimizer select one index instead of another (~~MDEV-7239~~)

roberto spadim added a comment - 2014-12-01 14:11 - edited yeap data engine didn't change optimizer i got as aria/myisam from a replicated server i tryed with innodb from master and same problem, i think it's related to some optimizer cost function selecting the "item" index instead of "rendimento" index, i don't know if optimizer prefer primary key columns is it possible? i will include a new mdev to explain better why optimizer select one index instead of another ( MDEV-7239 )

Elena Stepanova added a comment - 2014-12-01 14:19

Switched to 10.0.15 (release tag). Same results

MariaDB [test]> SELECT COUNT(*) AS GOOD FROM t1 a force index(rendimento), t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y';

+------+

| GOOD |

+------+

|    0 |

+------+

1 row in set (0.24 sec)

{code:sql}

MariaDB [test]> SELECT COUNT(*) AS BAD FROM t1 a, t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y';

+-----+

| BAD |

+-----+

|   0 |

+-----+

1 row in set (2.78 sec)

Elena Stepanova added a comment - 2014-12-01 14:19 Switched to 10.0.15 (release tag). Same results MariaDB [test]> SELECT COUNT (*) AS GOOD FROM t1 a force index (rendimento), t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' ; + ------+ | GOOD | + ------+ | 0 | + ------+ 1 row in set (0.24 sec) {code:sql} MariaDB [test]> SELECT COUNT (*) AS BAD FROM t1 a, t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' ; + -----+ | BAD | + -----+ | 0 | + -----+ 1 row in set (2.78 sec)

roberto spadim added a comment - 2014-12-01 14:24 - edited

@elena
i upgrade from a mariadb 10.0.12 (or 10.0.5, i don't remember) and got this problem
maybe trying an older release we can find what commit cause this problem?

roberto spadim added a comment - 2014-12-01 14:24 - edited @elena i upgrade from a mariadb 10.0.12 (or 10.0.5, i don't remember) and got this problem maybe trying an older release we can find what commit cause this problem?

Elena Stepanova added a comment - 2014-12-01 14:24

i will include a new mdev to explain better why optimizer select one index instead of another (~~MDEV-7239~~)

rspadim,
Please don't generate multiple JIRA issues about the same problem. If you want to add more details about the failure you have already filed, use comments. If you want to share general thoughts about what might be wrong with optimizer, please email to the mailing lists.

Elena Stepanova added a comment - 2014-12-01 14:24 i will include a new mdev to explain better why optimizer select one index instead of another ( MDEV-7239 ) rspadim , Please don't generate multiple JIRA issues about the same problem. If you want to add more details about the failure you have already filed, use comments. If you want to share general thoughts about what might be wrong with optimizer, please email to the mailing lists.

roberto spadim added a comment - 2014-12-01 14:33

hi elena, no problem, it's not about this mdev, it's about a new optimizer 'feature' (i didn't found this in mysql/mariadb) got this idea now from this mdev cause i was executing explain ... force index ( first index), force index (second index) .... to get information about the optimizer, i will contact maria-discuss/developer to check if this is a relevant mdev or not

about this mdev(7125) this problem happen after a mariadb upgrade (from 10.0.5 or 10.0.12 i don't remember now) maybe we can find the commit that cause this bug getting back to 10.0.5 and check what changed?

roberto spadim added a comment - 2014-12-01 14:33 hi elena, no problem, it's not about this mdev, it's about a new optimizer 'feature' (i didn't found this in mysql/mariadb) got this idea now from this mdev cause i was executing explain ... force index ( first index), force index (second index) .... to get information about the optimizer, i will contact maria-discuss/developer to check if this is a relevant mdev or not about this mdev(7125) this problem happen after a mariadb upgrade (from 10.0.5 or 10.0.12 i don't remember now) maybe we can find the commit that cause this bug getting back to 10.0.5 and check what changed?

Elena Stepanova added a comment - 2014-12-01 14:42 - edited

Depends on who you mean by "we"
If you want, you can do it, of course.
With the bug filed this way, I can't afford it.
Loading this dump on a previous (unknown) version and going up revision by revision through 700 revisions is extremely time-consuming, it isn't worth it.
Bisecting would be even more time-consuming because I would have to load dump over and over again (cannot really downgrade the datadir and expect it to be a clean experiment).

Elena Stepanova added a comment - 2014-12-01 14:42 - edited Depends on who you mean by "we" If you want, you can do it, of course. With the bug filed this way, I can't afford it. Loading this dump on a previous (unknown) version and going up revision by revision through 700 revisions is extremely time-consuming, it isn't worth it. Bisecting would be even more time-consuming because I would have to load dump over and over again (cannot really downgrade the datadir and expect it to be a clean experiment).

roberto spadim added a comment - 2014-12-01 14:55

i didn't checked how many revisions was done before thinking about it
hum could i help with this anyway?

roberto spadim added a comment - 2014-12-01 14:55 i didn't checked how many revisions was done before thinking about it hum could i help with this anyway?

Elena Stepanova added a comment - 2014-12-01 15:07

could i help with this anyway?

Will request information if necessary, no worries.

Elena Stepanova added a comment - 2014-12-01 15:07 could i help with this anyway? Will request information if necessary, no worries.

roberto spadim added a comment - 2014-12-01 15:14

ok, i tested with innodb and got the same index choise

roberto spadim added a comment - 2014-12-01 15:14 ok, i tested with innodb and got the same index choise

Elena Stepanova added a comment - 2014-12-01 16:16

Back to business...

Switched all tables to MyISAM. Numbers are slightly different, but the problem is still present:

MariaDB [test]> SELECT COUNT(*) AS BAD FROM t1_myisam AS a, t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y';

+-----+

| BAD |

+-----+

|   0 |

+-----+

1 row in set (1.95 sec)

MariaDB [test]> SELECT COUNT(*) AS GOOD FROM t1_myisam a force index(rendimento), t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y';

+------+

| GOOD |

+------+

|    0 |

+------+

1 row in set (0.25 sec)

Elena Stepanova added a comment - 2014-12-01 16:16 Back to business... Switched all tables to MyISAM. Numbers are slightly different, but the problem is still present: MariaDB [test]> SELECT COUNT (*) AS BAD FROM t1_myisam AS a, t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' ; + -----+ | BAD | + -----+ | 0 | + -----+ 1 row in set (1.95 sec) MariaDB [test]> SELECT COUNT (*) AS GOOD FROM t1_myisam a force index (rendimento), t2 AS b, t3 AS c WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND c.plano_conta_id_red=a.cfop_id_red AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' ; + ------+ | GOOD | + ------+ | 0 | + ------+ 1 row in set (0.25 sec)

roberto spadim added a comment - 2014-12-01 16:21 - edited

elena, i don't know where is json explain of mariadb development, do you know where i could download (even not yet fully implemented) just to check if json explain could help here i think it's one git 10.1 tree, but i don't know what branch/commit

roberto spadim added a comment - 2014-12-01 16:21 - edited elena, i don't know where is json explain of mariadb development, do you know where i could download (even not yet fully implemented) just to check if json explain could help here i think it's one git 10.1 tree, but i don't know what branch/commit

Elena Stepanova added a comment - 2014-12-01 16:25

Roberto, when/if we need more input, we will request it, you can count on it. For now, there is no need for you to do anything. Thanks.

Elena Stepanova added a comment - 2014-12-01 16:25 Roberto, when/if we need more input, we will request it, you can count on it. For now, there is no need for you to do anything. Thanks.

Elena Stepanova added a comment - 2014-12-01 16:50

Simplified a bit more (removed the third table):

MariaDB [test]> SELECT COUNT(*) AS BAD FROM t1_myisam a, t2 AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y';

+-----+

| BAD |

+-----+

| 863 |

+-----+

1 row in set (1.98 sec)

+------+-------------+-------+-------+-------------------------------------+------+---------+---------------------------------+------+----------+------------------------------------+

| id   | select_type | table | type  | possible_keys                       | key  | key_len | ref                             | rows | filtered | Extra                              |

+------+-------------+-------+-------+-------------------------------------+------+---------+---------------------------------+------+----------+------------------------------------+

|    1 | SIMPLE      | b     | index | NULL                                | id   | 16      | NULL                            |    2 |   100.00 | Using index                        |

|    1 | SIMPLE      | a     | ref   | item,transferencias,rendimento,giro | item | 6       | const,test.b.plano_conta_id_red | 5355 |   100.00 | Using index condition; Using where |

+------+-------------+-------+-------+-------------------------------------+------+---------+---------------------------------+------+----------+------------------------------------+

MariaDB [test]> SELECT COUNT(*) AS GOOD FROM t1_myisam a force index(rendimento), t2 AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y';

+------+

| GOOD |

+------+

|  863 |

+------+

1 row in set (0.26 sec)

+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+--------------------------------------------------------------+

| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows  | filtered | Extra                                                        |

+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+--------------------------------------------------------------+

|    1 | SIMPLE      | a     | range | rendimento    | rendimento | 4       | NULL | 47677 |    75.00 | Using index condition; Using where                           |

|    1 | SIMPLE      | b     | index | NULL          | id         | 16      | NULL |     2 |   100.00 | Using where; Using index; Using join buffer (flat, BNL join) |

+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+--------------------------------------------------------------+

Elena Stepanova added a comment - 2014-12-01 16:50 Simplified a bit more (removed the third table): MariaDB [test]> SELECT COUNT (*) AS BAD FROM t1_myisam a, t2 AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' ; + -----+ | BAD | + -----+ | 863 | + -----+ 1 row in set (1.98 sec) + ------+-------------+-------+-------+-------------------------------------+------+---------+---------------------------------+------+----------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ------+-------------+-------+-------+-------------------------------------+------+---------+---------------------------------+------+----------+------------------------------------+ | 1 | SIMPLE | b | index | NULL | id | 16 | NULL | 2 | 100.00 | Using index | | 1 | SIMPLE | a | ref | item,transferencias,rendimento,giro | item | 6 | const,test.b.plano_conta_id_red | 5355 | 100.00 | Using index condition; Using where | + ------+-------------+-------+-------+-------------------------------------+------+---------+---------------------------------+------+----------+------------------------------------+ MariaDB [test]> SELECT COUNT (*) AS GOOD FROM t1_myisam a force index (rendimento), t2 AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' ; + ------+ | GOOD | + ------+ | 863 | + ------+ 1 row in set (0.26 sec) + ------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+--------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+--------------------------------------------------------------+ | 1 | SIMPLE | a | range | rendimento | rendimento | 4 | NULL | 47677 | 75.00 | Using index condition; Using where | | 1 | SIMPLE | b | index | NULL | id | 16 | NULL | 2 | 100.00 | Using where ; Using index ; Using join buffer (flat, BNL join ) | + ------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+--------------------------------------------------------------+

Elena Stepanova added a comment - 2014-12-01 23:13 - edited

The structure of table b and extra indexes on table a are unimportant:

MariaDB [test]> show create table t2_min \G

*************************** 1. row ***************************

       Table: t2_min

Create Table: CREATE TABLE `t2_min` (

  `plano_conta_id_red` bigint(20) NOT NULL DEFAULT '0'

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

Also removed all indexes from t1_myisam except for rendimento and item.

MariaDB [test]> SELECT COUNT(*) AS GOOD FROM t1_myisam a force index(rendimento), t2_min AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y';

+------+

| GOOD |

+------+

|  863 |

+------+

1 row in set (0.29 sec)

+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+------------------------------------------------------------------------+

| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows  | filtered | Extra                                                                  |

+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+------------------------------------------------------------------------+

|    1 | SIMPLE      | b     | ALL   | NULL          | NULL       | NULL    | NULL |     2 |   100.00 |                                                                        |

|    1 | SIMPLE      | a     | range | rendimento    | rendimento | 4       | NULL | 40418 |    75.00 | Using index condition; Using where; Using join buffer (flat, BNL join) |

+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+---------------------------------

MariaDB [test]> SELECT COUNT(*) AS BAD FROM t1_myisam a, t2_min AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y';

+-----+

| BAD |

+-----+

| 863 |

+-----+

1 row in set (1.94 sec)

+------+-------------+-------+------+-----------------+------+---------+---------------------------------+------+----------+------------------------------------+

| id   | select_type | table | type | possible_keys   | key  | key_len | ref                             | rows | filtered | Extra                              |

+------+-------------+-------+------+-----------------+------+---------+---------------------------------+------+----------+------------------------------------+

|    1 | SIMPLE      | b     | ALL  | NULL            | NULL | NULL    | NULL                            |    2 |   100.00 |                                    |

|    1 | SIMPLE      | a     | ref  | item,rendimento | item | 6       | const,test.b.plano_conta_id_red | 5355 |   100.00 | Using index condition; Using where |

+------+-------------+-------+------+-----------------+------+---------+---------------------------------+------+----------+------------------------------------+

Elena Stepanova added a comment - 2014-12-01 23:13 - edited The structure of table b and extra indexes on table a are unimportant: MariaDB [test]> show create table t2_min \G *************************** 1. row *************************** Table : t2_min Create Table : CREATE TABLE `t2_min` ( `plano_conta_id_red` bigint (20) NOT NULL DEFAULT '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) Also removed all indexes from t1_myisam except for rendimento and item. MariaDB [test]> SELECT COUNT (*) AS GOOD FROM t1_myisam a force index (rendimento), t2_min AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' ; + ------+ | GOOD | + ------+ | 863 | + ------+ 1 row in set (0.29 sec) + ------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+------------------------------------------------------------------------+ | 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | | | 1 | SIMPLE | a | range | rendimento | rendimento | 4 | NULL | 40418 | 75.00 | Using index condition; Using where ; Using join buffer (flat, BNL join ) | + ------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+--------------------------------- MariaDB [test]> SELECT COUNT (*) AS BAD FROM t1_myisam a, t2_min AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' ; + -----+ | BAD | + -----+ | 863 | + -----+ 1 row in set (1.94 sec) + ------+-------------+-------+------+-----------------+------+---------+---------------------------------+------+----------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ------+-------------+-------+------+-----------------+------+---------+---------------------------------+------+----------+------------------------------------+ | 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | | | 1 | SIMPLE | a | ref | item,rendimento | item | 6 | const,test.b.plano_conta_id_red | 5355 | 100.00 | Using index condition; Using where | + ------+-------------+-------+------+-----------------+------+---------+---------------------------------+------+----------+------------------------------------+

Elena Stepanova added a comment - 2014-12-02 00:07

Presence of index 'item' is important. If there is no 'item', 'rendimento' is picked voluntarily, without forcing:

MariaDB [test]> SELECT COUNT(*) AS BAD FROM t1_myisam a, t2_min AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y';

+-----+

| BAD |

+-----+

| 863 |

+-----+

1 row in set (0.24 sec)

+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+------------------------------------------------------------------------+

| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows  | filtered | Extra                                                                  |

+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+------------------------------------------------------------------------+

|    1 | SIMPLE      | b     | ALL   | NULL          | NULL       | NULL    | NULL |     2 |   100.00 |                                                                        |

|    1 | SIMPLE      | a     | range | rendimento    | rendimento | 4       | NULL | 40418 |    75.00 | Using index condition; Using where; Using join buffer (flat, BNL join) |

+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+------------------------------------------------------------------------+

Elena Stepanova added a comment - 2014-12-02 00:07 Presence of index 'item' is important. If there is no 'item', 'rendimento' is picked voluntarily, without forcing: MariaDB [test]> SELECT COUNT (*) AS BAD FROM t1_myisam a, t2_min AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' ; + -----+ | BAD | + -----+ | 863 | + -----+ 1 row in set (0.24 sec) + ------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+------------------------------------------------------------------------+ | 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | | | 1 | SIMPLE | a | range | rendimento | rendimento | 4 | NULL | 40418 | 75.00 | Using index condition; Using where ; Using join buffer (flat, BNL join ) | + ------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+------------------------------------------------------------------------+

roberto spadim added a comment - 2014-12-02 00:09

i didn't tested... item_id/item_id_red columns are part of primary key, if you remove primary key and leave item and rendimento index, optimizer prefer item index?

roberto spadim added a comment - 2014-12-02 00:09 i didn't tested... item_id/item_id_red columns are part of primary key, if you remove primary key and leave item and rendimento index, optimizer prefer item index?

roberto spadim added a comment - 2014-12-02 00:22 - edited

from "show index from est_mov_myisam":

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Index_type
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

roberto spadim added a comment - 2014-12-02 00:22 - edited from "show index from est_mov_myisam": 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

Elena Stepanova added a comment - 2014-12-02 14:26

psergey,

As requested, results (no need to go through previous comments, they are intermediate steps).

The problem is that the query uses ref access with the index `item` on the table `a`, while range with index `rendimento` provides significantly better performance for this query. It is reproducible. Depending on the query, performance difference varied from 1.5x to 30x and more (in some cases I didn't wait for the "bad" query to finish).
The query is simplified (see below) to leave only one join and only AND conditions. No subqueries. Also, all select items have been replaced by a single COUNT to make the result set nicer.
The table structures are simplified to only have columns and indexes that directly participate in the query. Also, tables are switched from Aria to MyISAM, and partitioning is removed.
The data for the second table is reduced to 2 rows.
The data for the first table (the one with indexes in question) is shortened a bit, to 20 mln rows (the original data was 29 mln rows). It's also possible to reduce it to some 15 mln and still have the difference in plans, but the performance difference becomes negligible

At this point, the performance difference on my machine is ~3x (0.13 vs 0.35 sec). On another machine ~4x (0.07 vs 0.30 sec). This is for "warm" queries, cold ones can take considerably longer and are less predictable.
Below the "BAD" query is the one where the wrong index/access is used. The "GOOD" query is where the desired index is enforced.

MariaDB [test]> select count(*) from t1_min;

+----------+

| count(*) |

+----------+

| 20000000 |

+----------+

1 row in set (0.00 sec)

MariaDB [test]> select count(*) from t2_min;

+----------+

| count(*) |

+----------+

|        2 |

+----------+

1 row in set (0.00 sec)

CREATE TABLE `t1_min` (

  `item_id` mediumint(8) unsigned NOT NULL DEFAULT '0',

  `item_id_red` mediumint(9) NOT NULL DEFAULT '0',

  `lote_rendimento_data` date NOT NULL DEFAULT '0000-00-00',

  `rendimento` enum('Y','N') NOT NULL DEFAULT 'N',

  KEY `item` (`item_id`,`item_id_red`),

  KEY `rendimento` (`rendimento`,`lote_rendimento_data`,`item_id_red`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `t2_min` (

  `plano_conta_id_red` bigint(20) NOT NULL DEFAULT '0'

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

MariaDB [test]> show index in t1_min;

+--------+------------+------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table  | Non_unique | Key_name   | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+--------+------------+------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| t1_min |          1 | item       |            1 | item_id              | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |

| t1_min |          1 | item       |            2 | item_id_red          | A         |        4672 |     NULL | NULL   |      | BTREE      |         |               |

| t1_min |          1 | rendimento |            1 | rendimento           | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |

| t1_min |          1 | rendimento |            2 | lote_rendimento_data | A         |        3518 |     NULL | NULL   |      | BTREE      |         |               |

| t1_min |          1 | rendimento |            3 | item_id_red          | A         |      338983 |     NULL | NULL   |      | BTREE      |         |               |

+--------+------------+------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

SELECT COUNT(*) AS GOOD FROM t1_min a force index(rendimento), t2_min AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y';

+------+

| GOOD |

+------+

|  863 |

+------+

1 row in set (0.13 sec)

| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows  | filtered | Extra                                                                  |

+------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+------------------------------------------------------------------------+

|    1 | SIMPLE      | b     | ALL   | NULL          | NULL       | NULL    | NULL |     2 |   100.00 |                                                                        |

|    1 | SIMPLE      | a     | range | rendimento    | rendimento | 4       | NULL | 52529 |    75.00 | Using index condition; Using where; Using join buffer (flat, BNL join) |

| Note  | 1003 | select count(0) AS `GOOD` from `test`.`t1_min` `a` FORCE INDEX (`rendimento`) join `test`.`t2_min` `b` where ((`test`.`a`.`item_id` = 67) and (`test`.`a`.`rendimento` = 'Y') and (`test`.`b`.`plano_conta_id_red` = `test`.`a`.`item_id_red`) and (`test`.`a`.`lote_rendimento_data` >= '2014-10-13') and (`test`.`a`.`lote_rendimento_data` < '2014-10-17')) |

SELECT COUNT(*) AS BAD FROM t1_min a, t2_min AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>="2014-10-13" AND a.lote_rendimento_data<"2014-10-17" AND a.rendimento='Y';

+-----+

| BAD |

+-----+

| 863 |

+-----+

1 row in set (0.35 sec)

| id   | select_type | table | type | possible_keys   | key  | key_len | ref                             | rows | filtered | Extra                              |

+------+-------------+-------+------+-----------------+------+---------+---------------------------------+------+----------+------------------------------------+

|    1 | SIMPLE      | b     | ALL  | NULL            | NULL | NULL    | NULL                            |    2 |   100.00 |                                    |

|    1 | SIMPLE      | a     | ref  | item,rendimento | item | 6       | const,test.b.plano_conta_id_red | 4280 |   100.00 | Using index condition; Using where |

| Note  | 1003 | select count(0) AS `BAD` from `test`.`t1_min` `a` join `test`.`t2_min` `b` where ((`test`.`a`.`item_id` = 67) and (`test`.`a`.`rendimento` = 'Y') and (`test`.`b`.`plano_conta_id_red` = `test`.`a`.`item_id_red`) and (`test`.`a`.`lote_rendimento_data` >= '2014-10-13') and (`test`.`a`.`lote_rendimento_data` < '2014-10-17')) |

I've uploaded the new dump to ftp.askmonty.org/private/mdev7125-smaller.dump.gz.

Elena Stepanova made changes - 2014-12-02 14:26

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Elena Stepanova made changes - 2014-12-02 14:26

Assignee

Elena Stepanova [ elenst ]

Sergei Petrunia [ psergey ]

roberto spadim added a comment - 2015-02-01 16:37

hi guys any news?

roberto spadim added a comment - 2015-02-01 16:37 hi guys any news?

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:51

Workflow

MariaDB v2 [ 58607 ]

MariaDB v3 [ 67178 ]

Sergei Golubchik made changes - 2021-12-06 21:35

Workflow

MariaDB v3 [ 67178 ]

MariaDB v4 [ 143446 ]

Sergei Golubchik made changes - 2022-09-08 09:54

Fix Version/s

10.0 [ 16000 ]

Sergei Golubchik added a comment - 2022-09-12 10:15

10.0 was EOLed in March 2019

Sergei Golubchik added a comment - 2022-09-12 10:15 10.0 was EOLed in March 2019

Sergei Golubchik made changes - 2022-09-12 10:15

Component/s		OTHER [ 10125 ]
Fix Version/s		N/A [ 14700 ]
Resolution		Won't Fix [ 2 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

People

Assignee:: Sergei Petrunia

Reporter:: roberto spadim

Votes:: 1 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2014-11-17 20:35

Updated:: 2022-09-12 10:15

Resolved:: 2022-09-12 10:15

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration