MySQL stored procedure vs php script

Question

I run a website for a jewelry wholesaler.

Prices for all of their products are calculated using current metal fixes, which are updated every night.

Currently, calculations for the website are being developed using the php include function, which works great under current circumstances.

There are about 10,000 products, but prices are calculated in real time (i.e. when a web page is requested). The calculations are simple, but there are many (around 50+), and I am worried that increasing traffic may slow down the current script.

I redesigned the site and wondered if it would be useful to create a procedure in MySQL to perform the calculations.

Perhaps it will be faster than the current PHP script? Does anyone know a good reading link about using procedures?

Answer 1

Here's a breakpoint with stored procedure vs php.

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

The stored procedure was 10 times slower.

You can also look at this:

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

Answer 2

If the reason you think about it is related to performance and scalability, I would recommend continuing with the calculation in PHP.

The reason for this is that regardless of whether there is a performance limitation on your PHP when you scale your web application, it is usually much easier to move to multiple web servers than to multiple database servers. Therefore, it is preferable to do more calculations in PHP and less in MySQL.

Besides the performance aspect, I still prefer to avoid stored procedures in favor of having logic in the application, because

• It may be less portable. The saved procedure adds the effort needed to deploy a new instance of your application.
• They are written in a language other than PHP, so the PHP developer cannot easily understand them.
• It can be difficult to keep them in control of the source.

Of course, these problems can be solved without enormous difficulties if you want to use stored procedures.

Answer 3

If you absolutely need to update the prices on each page request, and you are worried that there will be a lot of traffic on the site, I would not recommend stored procedures.

I would recommend caching the information you use (and it is difficult to develop it without knowing how you do it) in memory (possibly using memcached) and continue reading it with PHP.

I admit that I did not conduct comparative tests between stored procedures and PHP performance in RAM, but if the procedure does not directly affect your request, I recommend caching.

Answer 4

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

It’s unlikely that the current site will ever be able to solve the performance problem when the difference between the calculation speed in php and the calculation speed in the database is ever noticeable. If you were then, there is something fundamentally wrong with the site code. (This includes real-time currency conversions, if done).

Saying that saving calc in PHP is usually preferable since it is easier to control and debug. This requires web coders to know the information somewhat, but this is usually not a problem. 90% of code acceleration occurs on 10% of the code, and it would be simple enough for dba to identify requests that cause db to load if it ever happened.