Lulzim Lulzim - 7 days ago 6
MySQL Question

What are the benefits of creating Stored Procedures in SQL and MySQL?

I have a theoretical question.

I can't see any difference between declaring a function within a PHP file and creating a stored procedure in a database that does the same thing.

Why would I want to create a stored procedure to, for example, return a list of all the Cities for a specific Country, when I can do that with a PHP function to query the database and it will have the same result?

What are the benefits of using stored procedures in this case? Or which is better? To use functions in PHP or stored procedures within the database? And what are the differences between the two?

Thank you.

Answer

Some benefits include:

  • Maintainability: you can change the logic in the procedure without needing to edit app1, app2 and app3 calls.

  • Security/Access Control: it's easier to worry about who can call a predefined procedure than it is to control who can access which tables or which table rows.

  • Performance: if your app is not situated on the same server as your DB, and what you're doing involves multiple queries, using a procedure reduces the network overhead by involving a single call to the database, rather than as many calls as there are queries.

  • Performance (2): a procedure's query plan is typically cached, allowing you to reuse it again and again without needing to re-prepare it.

(In the case of your particular example, the benefits are admittedly nil.)