Tuesday, December 4, 2007

MySQL stored procedures

I've been using PHP and MySQL since quite some time now, recently got a chance to go deeper into stored procedures side of it. I am on my way to learn them and at this point of time I feel I was missing a lot by not knowing them, I could have optimized my previous projects to a good level had I known stored procedures before.
People who have worked on advanced databases like Oracle must have been used to use the stored procedures, but people working on MySQL didn't had the privilege of using them and it was only after version 5 that MySQL introduced Stored procedures.
Who should learn them: LAMP developers specially, and all those who are interested in optimizing MySQL database.
If you are keen to know why should you learn them: When you using stored procedures you can considerably reduce the network traffic between PHP and MySQL or to be more generic between the application and the database.
HOW ???
consider a simple example, in one of your PHP pages you make a query to the database and fetch record ID's based on a selection criteria then you use those IDs as input to fetch more records from some other table. this is what we generally do in most of such cases, however if we see closely that the output of first query was of no use to PHP and it was just to get data so that subsequent queries could be formed. I know what you must be thinking, "we could have used where clause in query..." agreed. but there are cases where you use output of one or more than one query to form a finally query and then execute it. at times also performing some simple operations on the intermediate output. Now consider another scenario where in you create a stored program inside the MySQL database, you give input to it, it carries out all the intermediate steps (queries) to derive to the finally result/result-set and then returns it to PHP, this time we have saved a considerable amount of data exchange between PHP and MySQL, hence speeding up the application.
that was one advantage. Another is that the business logic can be made abstract to the application, the programmers make standard procedure calls to access the data, without getting into details about the query. the Queries can be written be domain experts. this introduces a sense of stability and security enhancement also.
If you are interested in reading more about the same, O'Reilly - MySQL Stored procedure programming is a book that you should be looking for.

No comments: