[MDEV-7125] selecting wrong index - Jira

XML

Word

Printable

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

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.