[MDEV-278] SUBOPTIMIZED QUERY PLAIN Created: 2012-05-17  Updated: 2012-06-22  Resolved: 2012-06-22

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Trivial
Reporter: roberto spadim Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

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;



 Comments   
Comment by Elena Stepanova [ 2012-05-17 ]

Hi,

5.2.4 is 1.5 years old.. Is the problem reproducible on any recent release (be it 5.2.12 or 5.3.7 or 5.5.23)?
You can find them here: http://downloads.mariadb.org/mariadb/

Thank you

Comment by roberto spadim [ 2012-05-17 ]

hi, starting download of 5.5.23
just some moment i will redo the query and check results
a doubt about jira... there's some place that i can 'end' this 'bug' report if the result is ok?

Comment by Elena Stepanova [ 2012-05-17 ]

>> a doubt about jira... there's some place that i can 'end' this 'bug' report if the result is ok?

You might have the button "Close issue" at the top of the screen. If you don't, just leave a comment here and we'll close it.
But lets see first how the query works for you on the recent version.

Comment by roberto spadim [ 2012-05-17 ]

nice no problem, just after check i will report, like mysql old ages
if 'bug' isn't closed and no reply is done, there's a 'waiting return' status?

Comment by Elena Stepanova [ 2012-05-17 ]

>> if 'bug' isn't closed and no reply is done, there's a 'waiting return' status?

I don't think so, the list of status values seems to be pretty short – it's pretty much just Open, In Progress and Closed (well, and Re-opened). And when someone closes a bug, there is also a 'Resolution' value which explains why the bug gets closed (Fixed, Won't fix etc.).

Comment by roberto spadim [ 2012-05-17 ]

Hi, i forgot the cfop_itens table, it's on:
http://spd1.agraagroindustrial.com.br:3080/mariadb-log/tmp_cfop.sql

well i put the new mariadb 5.5.23 in another machine and dumped the table to it... it´s not like the dell server, but can give results too
well. bad news... no optimization at all
the results are in

http://spd1.agraagroindustrial.com.br:3080/mariadb-log/MARIA-mdev278-results.txt

in less words...

--------------------------------------------
1)MARIADB 5.2.4 (WITHOUT FORCE INDEX) - dell server
QUERY TIME WITHOUT EXPLAIN=2,469s
--------------------------------------------
2)MARIADB 5.2.4 (WITH FORCE INDEX) - dell server
QUERY TIME WITHOUT EXPLAIN=0.453s
--------------------------------------------
3)MARIADB 5.5.23 (WITHOUT FORCE INDEX) - runing a backup machine (not a dell server)
QUERY TIME WITHOUT EXPLAIN=6,625s
--------------------------------------------
4)MARIADB 5.5.23 (WITH FORCE INDEX) - runing a backup machine (not a dell server)
QUERY TIME WITHOUT EXPLAIN=0,016s

Comment by Elena Stepanova [ 2012-05-18 ]

Hi,

I got pretty much the same results as you (apart from the absolute numbers). Your last observation explains it.

It is not OPTIMIZE itself that resolves the problem, it is the part of it that analyzes the table and updates the statistics. You can run ANALYZE instead and get the same result.
Optimizer makes its choices based on statistics it has for the tables. If a table gets updated (rows inserted or deleted), after a while statistics might become wrong. That's what ANALYZE command is for. You can see how information about indexes changes if you run SHOW INDEX before and after ANALYZE.

For this particular query, running ANALYZE on spamov_itens makes the difference, but you might want to run it on all tables, just in case.

Comment by Elena Stepanova [ 2012-05-18 ]

If you didn't run it before at all (or haven't run for very long time), I suggest to try to run it once and then see how it goes. Depending on the workload, it's quite possible that you will only need to do it once in a few months, which I expect you should be able to afford (you do have backup times, maintenance windows etc., right?)

Comment by roberto spadim [ 2012-05-25 ]

yes, i need check time to analyse table no problem...
now, a doubt...
i run this test in a 'new machine', with just mysql tables
i created the table, made a lot of insert (populate), and execute the select this give a bad query plain, i analyse it, and requery, and i have a good plain
since it don´t have a update and delete, why index statistic is 'out of date'?

Comment by Elena Stepanova [ 2012-05-31 ]

Hi,

Sorry, missed your question before.

For MyISAM tables, accurate statistics is not available until you executed ANALYZE at least once, so you can easily have bad results even if you didn't do update/delete. Please see bug http://bugs.mysql.com/bug.php?id=34467 for more information.

Comment by roberto spadim [ 2012-06-01 ]

humm in others words...
if i do
create table
analyse
inserts....

it will run better?

Comment by Elena Stepanova [ 2012-06-01 ]

No, you need to run ANALYZE after INSERTs. ANALYZE collects cardinality data, if you run it on an empty table, the results will become useless after inserting data.

Comment by roberto spadim [ 2012-06-01 ]

hum... what bug says (34467) is that innodb can do analyze online without analyze table, and myisam and head don't, that's right? in other words i will need a time to run it

what about aria, it's execute like myisam?

Comment by Elena Stepanova [ 2012-06-01 ]

Aria behaves pretty much like MyISAM in this regard. In case of Aria, for partial workaround suggested by James Day in the comment of [8 Nov 2010 13:10], you'll need to use aria_max_sort_file_size instead of myisam_max_sort_file_size.

Comment by Elena Stepanova [ 2012-06-15 ]

Hi Roberto,

Has ANALYZE fixed your problem? Can we close the bug?

Thanks.

Comment by roberto spadim [ 2012-06-15 ]

hi elena, yes it worked fine after analyze

i didn´t tested it again, i will test with the icp switch off (like the mdev-337), maybe icp is the problem

Comment by roberto spadim [ 2012-06-15 ]

yes, please close, any problem i open another bug

Comment by Elena Stepanova [ 2012-06-15 ]

In the absence of better 'resolution' value, closing as 'Won't fix' (should have been 'Not a bug').

Generated at Thu Feb 08 06:27:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.