Details
-
Bug
-
Status: Closed (View Workflow)
-
Trivial
-
Resolution: Not a Bug
-
None
-
None
-
None
-
None
-
VERSION IS 5.2.4 - DOWNLOAD FROM MARIADB WEB SITE
LINUX DISTRO = ARCHLINUX
HARDWARE = DELL SERVER, X86_64
Description
Hi guys, i don't know if it's the right place to post it, but... if not tell me where to post, or not post...
I have a server with mariadb (a bit old version 5.2.4) and it executed a bad plain in query, could you check why it's suboptimized?
well it's a big table... i will post where to download data
here is the query:
(if i remove the FORCE INDEX, mariadb take about 2.6 seconds, with FORCE INDEX it's take about 0.09 seconds, without query cache hit)
explain
|
SELECT COUNT(*) FROM ( |
SELECT a.lote_tipo,a.lote_spa,IF(a.cur_oe=0,0,a.cur_oe || ';' || a.cur_oe_seq) AS oe,a.cliente_tipo,a.cliente_id,a.distribuidor_tipo,a.distribuidor_id,SUM(b.quantidade) AS quant,SUM(b.quant_lib) AS quant_lib,SUM(b.quant_rejeitado) AS quant_rejeitado,SUM(b.quant_oe) AS quant_oe,SUM(b.cur_oe_quant) AS quant_cur_oe,SUM(b.quant_embarcado) AS quant_emb,SUM(b.quant_producao) AS quant_op,SUM(b.cur_op_quant) AS quant_cur_op,SUM(b.producao_quant) AS quant_producao,SUM(b.quant_produzido) AS quant_produzido,a.sit_pcp,a.sit_spa,a.sit_logistica AS sit_oe,a.sit_nf,a.data_prevista_embarque,SUM(b.cur_oe_quant_emb) AS quant_cur_oe_emb,( |
SELECT plano_conta_numero FROM cfop_itens WHERE plano_conta_id=a.cfop_id AND plano_conta_id_red=a.cfop_id_red) AS cfop_numero,a.saida_entrada,a.abate,a.orcamento_numero,a.compra_cancelada,a.data_digitacao |
FROM spamov AS a |
# FORCE KEY(spamov_data_digitacao) |
,spamov_itens AS b |
WHERE |
a.unidade_id=b.unidade_id AND a.lote_tipo=b.lote_tipo AND |
a.lote_spa=b.lote_spa AND a.lote_estorno=0 AND |
a.unidade_id=1000 AND a.lote_spa>=0 AND |
a.lote_spa<=99999999999 AND a.cur_oe>=0 AND |
a.cur_oe<=99999999999 AND |
a.sit_spa='mov' AND a.sit_pcp!='mov' AND |
a.data_digitacao>='2012-05-01 03:00:00' AND |
a.data_digitacao<='2012-06-01 02:59:59' AND |
a.data_prevista_embarque>='2000-01-01 02:00:00' AND |
a.data_prevista_embarque<='2014-01-01 01:59:59' |
GROUP BY b.unidade_id,b.lote_tipo,b.lote_spa |
) AS tmp_tbl; |