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

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 2

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 too much difficulty, but it all adds to the complexity overhead.