MySQL Stored Procedure vs PHP script

Question

I'm running a website for a jewellery wholesaler.

The prices on all their products are calculated using the current bullion metal fixes that are updated every night.

Currently, for the website, the calculations are worked out via a php include function, which works fine under current circumstances.

There are around 10,000 products, but the prices are calculated in real-time (ie when the web page is requested). The calculations are simple, but there are lots of them (Around 50+) and i'm worried that an increase in traffic may slow the current script down.

I'm redesigning the site and was wondering whether it would be beneficial to create a procedure in MySQL to do the calculations instead.

Is this likely to be faster that the current php script? Anyone know any good reading reference on using procedures?

Answer 1

If the reason you are thinking about this is due to performance and scalability, then I would recommend continuing the calculation in PHP.

The reason for this is that regardless whether there is a performance penalty in your PHP, when you are scaling your web application it is generally much easier to move to multiple web servers than multiple database servers. It is therefore preferable to do more calculation in PHP and less in MySQL.

Other than the performance aspect, I still generally prefer avoiding stored procedures in favour of having the logic in the application because

• It can be less portable. Stored procedure add to the effort required to deploy a new instance of your application.
• They are written in a different language than PHP, so a PHP developer may not find them easy to understand.
• It can be difficult to have them kept in source control.

These problems can of course all be solved, without a huge amount of difficulty, if you want to use stored procedures.

Answer 2

Here's a benchmark with stored procedure vs php.

http://mtocker.livejournal.com/45222.html

The stored procedure was slower by 10x.

You might also want to look at this:

http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html

Answer 3

If it is absolutely necessary to update the prices on every page request and you're worried that the site will be getting a lot of traffic I wouldn't recommend stored procedures.

I'd recommend caching the information you use (and it is hard to elaborate without knowing how you're doing this) in memory (perhaps using memcached) and keep reading it from PHP.

I'll admit that I haven't done any benchmarking between stored procedures vs in-memory PHP performance but if the procedure doesn't directly affect your query, I recommend caching.

Answer 4

In short, keep them in php. Easier to maintain.

For the current site, there is unlikely to ever hit a performance problem where the difference between the speed of calc in php vs the speed of the calc in the database is ever noticeable. If you were then there is something fundamentally wrong with the code of the site. (This includes realtime currency conversions if being done).

Saying that, keeping the calc in PHP is usually preferred as it is easier to control and debug. It does require the web coders to know the database somewhat but that is normally not a problem. 90% of the code speed ups happen on 10% of the code and it would be easy enough for a dba to identify the queries causing db load if it ever happened.