Wishes for new “Pure PHP” MySQL driverPeter Zaitsev
If you’re following MySQL or PHP landscape you should have seen announcement by MySQL to develop pure PHP driver. If not – Here is FAQ .
I’m to meet the team (Georg, Andrey etc) which will be developing this driver during my visit to Open Source Database Conference in November so I thought it would be good idea to gather some wish list for things nice to have in this new driver. Below is my list and I would appreciate to hear your ideas.
Build In Profiling I would like to see how many connections and queries page generated and how long they took – I will place this information in the log. For debugging I’d like to be able to get a table below page output listing all the queries, their exec times and number of rows they are returned. So far it has to be done in inherited class.
Auto Explain Would be very helpful for development to run EXPLAIN on the queries and warn if these is something obviously wrong.
Emulated Prepared Statements I would like to have easy time to switch from PS to standard statements to check bugs and performance difference without changing my application. Also there are bunch of statements which can’t be prepared – I would like library to take care of emulating these for me.
Arrays in Prepared Statements Dealing with certain types of queries, ie queries with IN is so painful with prepared statements – I would like to be able to simply able to pass data array and let library to take care about emulating.
Cached Prepared Statements It would be great if library could cache them so you do not have to worry of tracking same statements in your application and caching them.
Persistent Connections Ajax applications with frequent case of one query per request make persistent connections relevant again.
Automatic load balancing and fall back I would like to be able to pass list of servers system can connect to and let it handle load balancing and fall back by itself, similar as JDBC driver does.
Client size caching Would be very helpful for simple applications which may run query several times per page. I however would like to go further and have TTL based client cache with dynamic back end. I would expect many people would like to see memcached hooked up to it to share result sets among web servers for result sets which can be a bit stale. This might not be the most optimal way to cache things but it would be very easy for many applications.
Query Timeouts Make it easy to specify timeouts for connects and query execution so page would not timeout but you would rather be able to respond with “system overloaded try again later” message.
Exceptions This should be optional as not everyone likes them but would be very helpful in many cases.
Multiple concurrent queries I would like to be able to run multiple queries for multiple MySQL connections at once. For scale out applications data to generate the page may be on many different servers and it is latency killer to be required to query all of them sequentially instead of doing it in parallel. Now people have to do crazy things to work it around.
There are just few thing from the top of my head. There are probably some I forgot about at this point 🙂