cdukes
asked on
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.
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)
ASKER
It's not even close to what I need though - the table will hold about 100m rows.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have you tried put an index?
Try composite index on column id and column counter.
CREATE INDEX my_index ON logs (id, counter);
Try composite index on column id and column counter.
CREATE INDEX my_index ON logs (id, counter);
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!!
Placing index on counter field will definately solve the problem. It will impove speed.
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.
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?
ASKER
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 :-)
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 :-)
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