Menu
I'm doing a "select sum(foo) from bar" query on a MySQL database that's summing up 7.3mm records and taking about 22 seconds per run. Is there a trick to speeding up sums in MySQL?
771
Question: What is Faster, SUM or COUNT? Answer: Both are the same.
How Does it Work? The SUM MySQL function does precisely as the name indicates: it returns the sum of a set of values. The SUM function ignores NULL values when encountered in a set. If used in the SELECT clause where no row is returned, the resulting value from the SUM function is a NULL and not a zero.
No, you can't speed up the function itself. The problem here is really that you're selecting 7.3 million records. MySQL has to scan the entire table, and 7.3 million is a pretty big number. I'm impressed that it finishes that fast, actually.
A strategy you could employ would be to break your data into smaller subsets (perhaps by date? Month?) and maintain a total sum for old data that's not going to change. You could periodically update the sum, and the overall value could be calculated by adding the sum, and any new data that's been added since then, which will be a much smaller number of rows.
153
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
