0

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.

Sparsh
  • 3
  • 3
  • 1
    Do you want the count? or Sum of the Count? – Sashi Kant Nov 14 '14 at 05:39
  • What are you doing with this value? – ps2goat Nov 14 '14 at 05:41
  • @ps2goat I am returning it to a webpage – Sparsh Nov 14 '14 at 06:43
  • I ask what you are doing with it. E.g., if you are displaying counts for a single user, etc., or are you basically checking to see if any records exist. You seem to be worried about doing this query 10k times per second, which means this should probably be a cached value, if possible. – ps2goat Nov 14 '14 at 06:47
  • @ps2goat I keep getting requests which contains two ids and i need to return the sum of the counts of all ids between those two ids. And i get more than 10000 different pairs in a second, so efficiency is a big problem. – Sparsh Nov 14 '14 at 06:56

2 Answers2

1

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
ps2goat
  • 8,067
  • 1
  • 35
  • 68
  • Actually i want the sum of the count .. Sorry for the confusion.. Updated the description – Sparsh Nov 14 '14 at 06:41
  • Will indexing the count help?? – Sparsh Nov 14 '14 at 08:47
  • How often does the count value change? You could be slowing yourself down with continual re-indexing. – ps2goat Nov 14 '14 at 21:23
  • Also, Any idea how to improve the DB performance? I have increased the maxconnections to 500 and increased innodb_pool_size to 7G – Sparsh Nov 15 '14 at 04:45
  • Not according to this: http://stackoverflow.com/questions/437915/index-a-sum-column . Your best bet may be to cache the value in SessionState (which is per-user) to avoid repetitive calls for the same information. OR, you could create a batch process to update a lookup table during an inactive period. That lookup table could then be indexed for better performance. – ps2goat Nov 17 '14 at 15:59
0

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.

Akshat Singhal
  • 1,801
  • 19
  • 20