After my post on using PHP MySQLi and multiple prepared statements at the same time someone commented that using cursors could do the same thing. With that comment I dug some more and found that modifying the cursor type that is used under the covers will indeed let you execute multiple prepared statements concurrently on the same connection.
First off you may ask yourself why you would want to use this. The best answer I have for that is that the solution in the other post loads the entire result set into memory from the very start while with this solution you can control just how many rows you load. To get started you will want to take a look at the MySQLi statement set attribute call. This call is will let you modify the underlying cursor type that is used with the prepared statement in two ways that are useful for this issue.
While sprucing up the PHP code I use to provide my own Stack Overflow API for GeeQe I ran into an error caused by trying to use multiple prepared statements with MySQLi. It turned up when I tried to execute one prepared statement while looping over the result set from another prepared statement that were both created on the same connection. What came out was the following error:
"Commands out of sync; you can't run this command now"
Details about this error can be found in the mysql docs. Reading those details makes it clear that the result sets of a prepared statement execution need to be fetched completely before executing another prepared statement on the same connection.
While developing my GeeQE iPhone application I decided I needed a way to let users search posts so I started looking around for a simple search engine that I could use with PHP. I took a look at a number of different options like MySQL Full Text search, Sphinx, Solr and others based on Lucene. After looking at what it would take to get started with each I decided to go with Sphinx. Sphinx looked like it would be the easiest and quickest to set up, didn't require a lot of resources to run in an idle state and would integrate with PHP easily.
This post goes over how I went about configuring Sphinx and gives an example of how to integrate it with PHP. I'm using MySQL as the data store filled with the Stack Overflow CC data dump although it should be easy to adapt the instructions to other data sources. To follow along just download a copy of the data dump and use my schema and loader to get the same MySQL database.
I just finished reading a post to the Media Temple blog about their MySQL problems . I think it is an excellent example of what happens when you only have one side of the house trying to fix a problem. The post leaves out some details but they make it clear that they believe their problems were caused by badly written apps hammering the database. It sounds like they tried very hard to fix the issues on the hardware and MySQL side but couldn't so have switched the way they are provisioning the database systems to more isolate the problem sites. The moral of that story is that even when you are smart you can't always fix software problems on the systems side.
The Media Temple guys don't go into any great detail on their current shared MySQL system but I would think that if nothing else they ran into the problems listed in this post: performance of complex queries. At some point you just have too many people trying to hit your database for any one person to achieve efficiency.
Tags: mysql, administration