Mysql select sum() performance

Hi,
Can someone tell me how to get sum to calculate faster?

If I use SUM() is takes about 5 seconds, whereas if I use count(*) it's instant.
The problem is that my counter column is not always 1, so I need to get a total from the sum.


SELECT SUM(counter) from logs;
+--------------+
| SUM(counter) |
+--------------+
|      9993321 | 
+--------------+
1 row in set (4.38 sec)

SELECT count(*) from logs;             
+----------+
| count(*) |
+----------+
|  9992137 | 
+----------+
1 row in set (0.00 sec)

Open in new window

cdukesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi cdukes,

The two functions are probably using different sources for the data.  Count(*) could be coming from the table statistics or indexes, and sum(*) from indexes or the row data.

Either way, summing 10,000,000 rows in under 5 seconds seems quite impressive.


Kent
cdukesAuthor Commented:
It's not even close to what I need though - the table will hold about 100m rows.
SoLostCommented:
No, you can't speed up the function itself.  The problem is that you're selecting all 10,000,000 records.  MySQL has to scan all 10,000,000 records to get the information that you want.

Without adding more/faster hardware you could try creating a summary table.  If you want to filter the data even further, I assume that there's one or two columns from the log table that you could group and index the information by. e.g. Date, Username, Process etc.



Create triggers on the log table for INSERT, UPDATE and DELETE that will automatically keep this summary table up to date based on the information that is being added to the log.

Doing a SUM from this table should prove to be much faster.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

tikusbalapCommented:
Have you tried put an index?

Try composite index on column id and column counter.

CREATE INDEX my_index ON logs (id, counter);
theGhost_k8Database ConsultantCommented:
Alternative is you may use a trigger which updates maintain statistics of counter sum etc in a separate table  and you can get sum from there quite fast. Yep inserts will get affected a bit!!
pareshprajapatiCommented:
Placing index on counter field will definately solve the problem. It will impove speed.
SoLostCommented:
An index on the counter column will only improve speed when you're searching for values in the column.  It will not make any difference to summing the column.
pareshprajapatiCommented:
Index entries are stored on different location then table data. I have tested it and it worked fine for me. Can you please check with your database?
cdukesAuthor Commented:
Thanks,
I ended up writing a separate insert to keep track of the totals all together so that I didn't have to sum.
I have a backend perl script doing the inserts. I also maintain a table with various values for other things like charts, etc. so adding one more insert was quite simple.

Here what I used (perl dbi prepare statement) for anyone searching for help later on:
INSERT INTO cache (name,value,updatetime) VALUES ('msg_sum',?,?) ON DUPLICATE KEY UPDATE value=value + ?)

Now, to get a sum of all rows in the table, I simply query this one row...much faster :-)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.