Encapsulation vs obstacles - PHP support for MySQL Stored Procedures

I suppose that it's a nice skill to have - being able to write stored procedures.  I've used MySQL since version 3, but MySQL has only supported stored procedures since version 5.  I've not learnt to use them until fairly recently, since for the most part I've not found cause to use them as a PHP developer,

I've been doing some MySQL optimisation work for a client though, and I decided that the nature of the queries might justify taking the SQL complexity out of the PHP code and encapsulating it in stored procedures instead.  It was a decision that didn't last that long.

MySQL stored procedure problem 1: can't use prepared statements properly

Your typical PHP DAO query will use a variety of limits, perhaps a date range in the 'where' clause, for example. Unfortunately, the only way to build a query dynamically in a stored procedure is to concat() all the parts together, select the result into a prepared statement, execute that and then discard the results, like this:

SET @q = CONCAT("SELECT * FROM ",@abc," LIMIT 0,10;");

PREPARE stmt1 from @q;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

That in itself is frustrating and a little clunky.

The second problem - the query log

For optimisation purposes, there may be cases where stored procedures are quicker, but while you typically put more than one query in a stored procedure, MySQL's query log and slow queries log only show the call to the stored procedure, not the component queries.

This means that you need to fiddle around with the procedure, commenting out, echoing component queries and clearing the MySQL query cache each time, before you can discover the bottleneck.

The third problem with MySQL stored procedures - PHP support

To be honest, I should have investigated this about two days earlier.  The main issue with using stored procedures in PHP is the really poor support for them in general.  If you're using something like Symfony, or the DB package that actually does the work for Symfony and Propel, Creole, then you're limited to MSSQL if you want to use stored procedures.  Additionally, PHPMyAdmin doesn't support them very well at all, and the basic PHP libraries, mysql and mysqli only support them if you're prepared to dig deep into the docs.

The upshot of this is that for the forseeable future, I'll be using plain old DAO classes to encapsulate my DB code.

 


© eCreate Web Services Limited, 2008