In the book I’m currently writing, “Effortless E-Commerce with PHP and MySQL”, I’m using stored procedures for one of the two e-commerce sites being developed. Stored procedures, in case you’re not familiar with them, are blocks of code stored in the database. You can kind of think of them like defining your own functions in PHP, although I have to be careful in saying that as MySQL also supports stored functions, which are different in usage than stored procedures, but the premises are similar.
I’m using stored procedures for two reasons. First, they’re more secure, as they hide database details and create an interface that makes it impossible for hackers to adversely manipulate the database. Stored procedures also use a different permissions system, which is an added security benefit. Second, in the book’s example site, I use somewhat of an MVC (Model-View-Controller) approach, separating the data (MySQL), the visual interface (HTML), and the logic (PHP). (To be clear, the site does this without using Object-Oriented Programming or a framework.) The MVC design creates very clean, autonomous files (for example, there’s not an iota of HTML in the PHP scripts and the only queries run are along the lines of CALL stored_procedure_name()). Furthermore, the MVC-based site should scale well, as you can throw server resources at just the parts that need the most help. You can also apply specific caching techniques to each part of the equation.
I first wrote about stored procedures in the second edition of my MySQL: Visual QuickStart Guide, published in 2006, so some extra research was in order on what has changed since then. Along with the MySQL manual, I read a fair amount of MySQL Stored Procedure Programming by Guy Harrison and Steven Feuerstein. It’s a good book that I’d easily recommend (although it also came out in 2006). One of the book’s strengths is that, in the process of discussing stored procedures, it does an excellent job of explaining some of the internals as to how MySQL works. This includes discussions of optimizing any SQL query you might use, whether inside of a stored procedure or not (because the first way to optimize a stored procedure is to optimize the queries it runs).
I was also pleasantly surprised to find how efficiently stored procedures execute in today’s version of MySQL. Stored procedures were notoriously slow when they were first added to MySQL in version 5. But in tests on my computer (running MySQL 5.1.37), stored procedures executed from the command line often ran ten times faster than the procedure’s queries on their own. This is due to how MySQL caches stored procedures, no doubt. When the procedures were run from PHP scripts, the performance difference was even greater (in part because there’s less actual data for the PHP script to send to the database). So along with improved security and data separation, stored procedures today can offer a significant performance benefit.
This all comes at a cost, of course. Stored procedures put a lot more load on the database. Generally I’m an advocate of using the database server as much as you can, but you’d have to be aware of the potential that the database server becomes the bottleneck in your Web site. Second, to create and execute stored procedures, you’ll need a MySQL user with permission to do so.
If you’re not familiar with stored procedures, check out the MySQL manual, the aforementioned book, or search online. Or if you wait a couple of months, you can see them in action in my new e-commerce book!
