How much calculation should be done by MySQL?

Question

I am writing an application and using MySQL to return the difference between 2 dates in MySQL should MySQL do this or should I actually let PHP handle it?

I also just need the sum of all the results I am getting back should I return them and add them up on the php side or is there a way to add all the results together on the MySQL Server side?

Answer 1

It depends somewhat on the application, but in general, I'd push it to the PHP, because normally you're building a web site for multiple concurrent accesses; why put the calculation into the database and potentially have a bottle neck?

Answer 2

There's no good answer to that. I personally do as much as possible in SQL but mostly because I can, not because I should.

And yes, you can ask MySQL to calculate a sum:

SELECT id, SUM(price) FROM items GROUP BY id WITH ROLLUP

The last result, the one with id equals NULL, is going to contain sum for all the rows.

Answer 3

If it's thousands of different numbers I'd try to do it on the database side. What Charlie said is pretty much the usual. I often do calculations in a database and add them as an additional column just in case I needed to do server side sorting, but this is obviously not your case.

Answer 4

I think that you have two separate cases here. In the case where you are returning two values and performing a calculation on them, then doing that on the front end probably makes the most sense as long as it's not a complex calculation that requires significant business logic. If it does involve complex or specialized business logic then you should have a central place for that logic, whether it is in a business layer or in the database, so that it is done consistently. If you're just finding the difference between two dates or something, then just do it on the front end.

In the second case, where you are summing values, that sounds like something that should probably be done in the database. Networks tend to be much more of a bottleneck than modern day databases on today's hardware. Save sending a bunch of rows over the network just to add them up if you can just do it in the database.