CODEWITHSUNDEEP
mysql
user2320239
user2320239

Reputation: 1038

Why is SUM query is extremely slow even for small numbers of records?

Hi I have a table amounts that has about 30mil records in it. I want to be able to sum the values of certain subsets of those amounts. However mysql takes a extremely long time to do this.

For example if I run the query:

select amount from amounts limit 100;

I get the result in 0.0096s, however if I run:

select sum(amount) from amounts limit 100;

It takes 9.624s, if I run EXPLAIN I get the following:

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, amounts, , ALL, , , , , 28022610, 100.00,

I'm struggling to understand why it would take that long to sum 100 decimals?

Upvotes: 1

Views: 772

Answers (2)

Rick James
Rick James

Reputation: 142278

Perhaps you wanted

SELECT SUM(amount)
    FROM ( SELECT amount
               FROM amounts
               ORDER BY ...  -- see Tim's Answer
               LIMIT 100
         ) AS x;

That, unlike your sum, will add up only 100 items. First the "derived table" gathers 100 values; then the outer table does the summing.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521103

Taking your second query apart:

select sum(amount) from amounts limit 100;

Two things are happening here. First, you are asking MySQL to sum the amount column over the entire table. This necessitates touching every record in the table, and given that there is no WHERE filter, MySQL would likely not use any index, but would opt for a full table scan. Second, you have a LIMIT clause. But, since SUM(amount) just returns a single record/result, the LIMIT clause is superfluous and won't really do anything.

In your first query, you are running LIMIT without ORDER BY. This means that MySQL can, in theory, just pick the first 100 records it comes across and return them. This, in turn, might mean that it doesn't even have to scan the entire table, it can just do something like read the first few pages, whichever come first.

Upvotes: 2

Related Questions