• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1993
  • Last Modified:

MySQL very slow when I use the SUM function

When I run a MySQL query it takes 1 second to run.  If I use SUM goes upto 7-8 seconds.  On MS SQL 2000 same query takes 1 to 2 seconds.

I have indexing on issdate field only as btree (see test code below).

Any suggestions on using SUM.  The table has many fields and has approximately 90+ million records.

Thanks

runs slow:
SELECT SUM(segpax) segpax
FROM xxxdetail 
WHERE (issdate between '2011-05-01' AND '2011-05-31');

Runs fast:
SELECT segpax
FROM xxxdetail 
WHERE (issdate between '2011-05-01' AND '2011-05-31');

Open in new window

0
CPOINT2000
Asked:
CPOINT2000
  • 7
  • 4
  • 3
  • +2
1 Solution
 
joelsplaceCommented:
Have you checked the normal hardware things like processor use, memory, disk space & fragmentation?
0
 
CPOINT2000Author Commented:
I just started on MySQL about 2 weeks ago, using online to learn  - Google University :)  So I am not to familiar with MySQL.  I been a SQL 2000 user.  I have 12GB memory, disk space is not an issue, drive is not fragmented if that's what you mean.  I am going to have some one look at it later today for proper MySQL settings, tuning, etc.

However if you look at my code sample, when sum is not used it is fast, adding SUM in to combination just slows it down.  Even slower when query get complicated.  I was wondering if there is some special rule in MySQL for SUM option.

Thanks

0
 
HainKurtSr. System AnalystCommented:
what about these queries:

SELECT MAX(segpax) segpax FROM xxxdetail WHERE (issdate between '2011-05-01' AND '2011-05-31');
SELECT MIN(segpax) segpax FROM xxxdetail WHERE (issdate between '2011-05-01' AND '2011-05-31');
SELECT AVG(segpax) segpax FROM xxxdetail WHERE (issdate between '2011-05-01' AND '2011-05-31');
SELECT COUNT(*) segpax FROM xxxdetail WHERE (issdate between '2011-05-01' AND '2011-05-31');

is segpax a number or string?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
CPOINT2000Author Commented:
all those queries are slow also.  segpax is INT(11)
0
 
HainKurtSr. System AnalystCommented:
maybe it means

SELECT segpax
FROM xxxdetail
WHERE (issdate between '2011-05-01' AND '2011-05-31');

is fast because you are only retreiving first 20-50 record, not all records are retrieved. if you fetch all records it will be slow too...
0
 
HainKurtSr. System AnalystCommented:
try

STR_TO_DATE('01,5,2013','%d,%m,%Y'), hope it helps a bit...

SELECT SUM(segpax) segpax
FROM xxxdetail
WHERE issdate between STR_TO_DATE('2011-05-01','%Y,%m,%d') AND STR_TO_DATE('2011-05-31','%Y,%m,%d')

and you should have an index on this date column...
0
 
Kevin CrossChief Technology OfficerCommented:
This page may be of interest: http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html
You can try adding segpax to the index, but you can check what is going on via EXPLAIN. Post the results of that, so we can help ....
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> I have 12GB memory,
but I bet you did not modify the default parameters of your mysql server to actually use some memory:
http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html

this said, please show the explain plan of the 2 queries once you completed the tuning above and your SUM() query is still slow ...

you could, just for fun, try this one:
SELECT SUM(segpax+0) segpax
FROM (SELECT segpax FROM xxxdetail 
         WHERE (issdate between '2011-05-01' AND '2011-05-31')
  )

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Good point, a3! I thought of that earlier, but was going to leave to HainKurt. Forgot when I just posted. *sigh* That is why you are the king. *smile*
0
 
CPOINT2000Author Commented:
I tried STR_TO_DATE suggestion, did not see any change.  

(id)            1	
(select type)   SIMPLE	
(table)         xxxdetail	
(Type)          range	
(possible keys) issdate_rng_idx,date_iss_flt_rng_idx,date_iss_flt_od_idx	
(index)         date_iss_flt_od_idx	
(key length)    8		
(rows)          4588488	
(extra)         Using where; Using index

Open in new window

0
 
CPOINT2000Author Commented:
Thanks, I am looking into the last few comments now...
0
 
HainKurtSr. System AnalystCommented:
for example, I am running a query on oracle like

select * from members --> 30ms (gives first 40 records, then I can scroll which takes time of course)
select count(*) from members --> 3sec

for about 1M record... so looks like it is normal :)
0
 
CPOINT2000Author Commented:
HainKurt, thanks for checking.  I have someone looking into MySQL settings now.  Your comparison is fine with oracle run.  However I am switching from SQL 2000 to MySQL and notice some things are much faster on MySQL.  Queries also run equal speed or faster on MySQL.  But when I add the SUM command, speed drops drastically.  Yet same query with SUM on SQL 2000 runs in 1-2 seconds with about 90+ million records.  That's why I don't want to think it's normal.   I will reply back here once we look into the MySQL settings with progress.
0
 
Kevin CrossChief Technology OfficerCommented:
Here is another helpful link to go along with performance tuning:
http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
0
 
CPOINT2000Author Commented:
mw, thanks let me check it out...
0
 
CPOINT2000Author Commented:
This led me in right direction.  Problem is resolved.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now