CODEWITHSUNDEEP
mysqlsql
Sparsh
Sparsh

Reputation: 3

Calculate sum from MySQL server or from application

I have a MySQL table which has two columns : ID and count. It has an index on ID field.

Now if i have to get sum of all the count between two IDs, I can write a query like:

Select SUM(count) from table where id between x and y

or i can get 

select count from table where id between x and y

And then loop through the result and calculate the sum of the count on my application code

Which one is better, considering the speed is the essential thing here. Will indexing on the count help?? Or can i write a different SQL?

Would indexing on the count column help in any way?

I have around 10000 requests per second coming in and I am using a load balancer and 5 servers for this.

Upvotes: 0

Views: 166

Answers (2)

ps2goat
ps2goat

Reputation: 8485

The second one is the correct one. There's no need to sum a count, as the count comes back as a single value. It only needs to be run once.

Unless you have a column named count, in which you want to sum all the values...

EDIT Because you are saying you have a column named Count, you would use the first query:

Select SUM(count) from table where id between x and y

Upvotes: 1

Akshat Singhal
Akshat Singhal

Reputation: 1801

Use approach 1 as you would save on fetching data from MySQL and iterating over it.

The time taken by MySQL to execute either of your queries would be nearly the same but the second approach would require looping through the results and summing them; unnecessary overhead.

Upvotes: 0

Related Questions