Title sums it up. I'm reasonably well off with PHP, and comfortable getting it to do what I need it to with values pulled from MySQL, however in the interest of expanding my knowledge I've been wondering if perhaps it would be a more efficient technique in the long run. Are they equivalent (due to being roughly the same amount of processing in the end), or are there cases where one presents a distinct advantage over the other?

  • 3
    Good question & long standing point of discussion.. how much will be in APP side, how much will be done in DB side. You might want to rephrase it to "RDBMS to APP-Layer", because the question applies to all sorts of DB and APP interactions. – Sebastian Roth Jan 5 '11 at 10:42
  • 2
    I want to add to this question so bad, but I have nothing of interest atm to add. This is a great question, and one I wouldn't mind becoming a canonical faq – jcolebrand Jan 6 '11 at 5:08
  • I was actually contemplating asking this question, whether it would have been on-topic here for DBAs. I searched for something completely different and found this one by accident. So +1 for reading my mind – Derek Downey Jan 7 '11 at 1:10

It depends a lot on what you're doing with the records, and what your network architecture looks like. For example, if the stored procedure is going to reduce the overall amount of data transmitted from MySQL to PHP, and you have a relatively slow link between the PHP instance and the database, a SP could certainly help you.

I would recommend you start with some of the 'heavier' manipulations you do to your data on the PHP side, see what you can write as a SP, and then benchmark it.

| improve this answer | |

A point in favor of doing more processing in the database is that the database can't optimize what it doesn't see. As an example, setting a property of a persisted class to a constant value for all instances will definitely perform better if done in the database layer.

A point against is that databases are good at set-based operations, but not so good at row-based operations. For example, something like converting a UTC time to local time based on a time zone varying per row may not perform as well in the database as it might in the app layer.

| improve this answer | |
  • An excellent point about the optimizer. – TML Jan 13 '11 at 1:38

Usually if the result is an aggregation, (sum, avg, count) it is much faster to be computed in the database server, by avoiding unnecessary communication between the server and the client. Also, ordering and grouping can benefit of indexes, reducing the amount of computation.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.