CODEWITHSUNDEEP
mysqlaggregate-functions
user1028741
user1028741

Reputation: 2825

mysql performance in aggregation functions

I wonder about the aggregate functions of MySql.

Will the following query:

select sum(c / 1000) from t;

Have better performance than:

select sum(c) / 1000 from t;

when t is a big table?

Upvotes: 0

Views: 444

Answers (1)

MrMarlow
MrMarlow

Reputation: 856

I decided to test this, but I have no idea what your definition of "big table" is.

For this test case, I used a database table with 1,063,527 rows of data in (Again, your data set may be bigger)

The times were as follows;

SELECT sum(field / 1000) FROM table;

0.344   0.359   0.625   0.390   0.594   0.359

SELECT sum(field) / 1000 FROM table;

0.234   0.390   0.219   0.438   0.203   0.485

Test Conditions

For each of the calls, I changed the division number in the hope that it would avoid a "cache" as the result set would be different. So /1000,999,998 etc.

I'm sure there are people who can do far better checks and have far bigger tables to query against, but I wanted to try it.

Conclusion

Personally, I don't see any difference with the kind of ranges both produced - again, this could change when you multiple the data set by (n)

Upvotes: 3

Related Questions