MySQL Prepared Statements

I recently developed a PHP script to export some denormalised data out of a set of MySQL tables. The original authors of the system we storing PHP serialised arrays in the database. The data inside that array was useful to me for the queries that I was performing. Now I could have done some tricky stuff taking data from MySQL into PHP and back again but I decided to hack my way through deserialising the array using MySQL string manipulation functions and CASE statements all intermingled.

I’m the first to admit, it’s not that maintainable and looking at the SQL you have no idea what it does. It works and it is a very long SQL query indeed. The query comes to a few KB it is that big but MySQL doesn't seem to mind the query as it is relatively fast when running it through explain.

I have been running this query many times against a database located in an external location and discovered that the outgoing bandwidth was actually quite large because of the size of the query. Given that the query only changes a couple of values on each run it is a great candidate for a stored proceedure. That is declare the statement to MySQL, mark some variables and then when you want the large query run just run the procedure and pass the variables in.

Needless to say the script now uses a lot less bandwidth talking to the remote server.

In one of my data export scripts this week, I have an array of months to process. It is about 30 months long. For each month there are two sets of ids that I query. The id list can be about 200 long. So I ended up generating two stored procedures for each month and then ran the query for approximately 1.2 Million memebrs. That saved a lot of bandwidth and also appears to have sped things up a bit.

When the query is large and run often, go straight to MySQL stored procedures. Especially if your database is being access across a WAN / VPN.