Reducing database queries

I found a real badass design flaw on my newly created ORM system the other day. On the register form for a movie, the page had 119 queries taking about 0.085 seconds of running time. Since the site is not finished and the movie I was editing was a simple test, this would have been maybe 250+ queries for one movie, and the data would have been much more on in the tables so the time of each query probably would have been much higher in a real live situation. Have this in mind. What I had done, was to fetch the relations in the many-to-many table with one query each(so that I could use the same get method for every object, including relations). Really dumb, but I did not think it would affect that much in the beginning as long as each of them were fast.

Well, it took time, and I rewrote the system to get every relation in one query per table – reducing the amount of queries to 51 and the time went down to about 0.040 seconds for the page load. That made me think, 51 still sounded a lot to me, so I decided to analyze more. I noticed then that I made the same query many times to some of the tables. For instance, the country table, where I fetched all the objects every time I needed the list. And that list I needed in many places, more precise, in many of the relations. The solution to this began as a crazy simple idea that worked.

What did I do?

In the object that fetched the countries ( SELECT * FROM country ) and made the country object list of it (as mentioned, unfinished and short list), I added a private static $table = array(); at the top. Then I simply used logic from the singleton pattern. I checked if the self::$table['country'] was set with the list, if yes, I used the old list – if no, I created the list.

The amount queries was reduced to 33, and time to 0.035 seconds.

I still need to see if this is a good solution in the long run, as the time to execute the page varied a bit more than before. Can’t wait to see how it performs when the tables gets filled with data.

Database connection speed in php

So, on this current project of mine, I have one unfinished page that have currently 31 fast mysql queries(to a database that contain a very little data) – and I wanted to see how different settings would affect the speed. I have heard that connections are costly, but how costly are they really? These are the average results of my little test:

One connection for each query: 0,045 sec
One persistent connection for each query: 0,025 sec
One connection used in a Singleton pattern: 0,0175 sec
One persistent connection used in a Singleton pattern: 0,0165 sec

Summary:
The old method is the slowest by far! If you have many concurrent users on the webpage persistent connection will help a bit. But the best effect is from using a Singleton connection – or another method where you use one instance of the connection on all the queries. Persistent connection uses some time fetching the connection from mysql on the server, so it’s faster to do it in code.