Website tactics: A question about how many SQL queries are enough

Question

I've recently begun to unveil and slowly roll out a homemade CMS. The site allows a lot of customization with movement towards internationalization and customization onto a level that doesn't require source code. This is a personal project, and the entire intent was to see how far I can push my own programming limits (the question of distrubtion of a CMS that handles blog, webcomic, and a small forum isn't one that I'm willing to consider, not until I clean it up and work on it some more--as well, seeing as it's an amateur project, I doubt it has any gravity compared to other, more refined projects... but those are not topics that concern the topic at hand.)

I've instituted a series of code that allows me to see how fast each page is generated and how many queries are ran; on average, I'm seeing 9-13, upwards to 12 MySQL queries performed per page. Average time to generate a page is somewhere between 10-20 ms. Now, not having any experience with professional design, what is the optimum that I should be striving for?

What are ways to reduce generation time (or, with an average of 15 ms/page, is this not even a concern), or tactics on reducing the number of a queries on a page where most of the content is loaded FROM an MySQL database, including things like menu items.

Mind you, this is a very broad question; it isn't my intent to ask a general question or spark conversation, but to find out ways of reducing the load (if any) on a server that such a system could create.

Answer 1

If you are mainly developing these sites for small companies or other reasons where you don't predict or imagine a high traffic (i.e. nothing like Digg/Facebook/etc) then an average of 15ms should be fine.

May I ask what the 12 queries are for? I imagine they are for getting menus items, getting page content and the like. There are various methods of combining/optimising queries, so if you perhaps post a few I (and other stackers) may be able to help you optimise your queries.

Answer 2

It depends...as it always does with performance questions, if the system currently meets your performance requirement then don't worry too much.

Generally if your page generation time is 15ms it will only be a fraction of the total click to glass time that the user experiences, see the yahoo exception performance pages There will be other things to look at in order to get the fastest possible page load time.

On the server, the chances are that the db is going to be caching the results to nearly all if not all of the queries you are running, hence the very fast page timing. You might want to load up a larger set of data if you haven't already done so to test the app, you might find a performance will degrade with the size of the data set.

Answer 3

• Using a PHP opcode cache will dramatically cut down on the time taken to open and compile PHP scripts, by skipping the parsing and compilation into bytecode.

• Turning on the MySQL query cache is generally (though not always) a good idea.

• Rather than focusing on the number of queries, focus on reducing the time those queries take by optimising your queries. It is often much more efficient to have a larger number of small, optimised queries than to try and reduce the number of queries.

• Use a profiler such as the one built in to XDebug. Together with an interpreter like KCacheGrind or WinCacheGrind, optimising code really helps when you know what to focus on. It's not worth optimising something that contributes only a negligible amount to your total execution time. It's worth getting to know what everything in *CacheGrind means.

My PHP content management system usually loads a page in about the same amount of time (down to minimum 8ms where everything is a cache hit). But very occasionally, when you do something complex it may take over 500ms. When concerned about user experience the typical time is more important, not so much the outliers, but when concerned about server load the average time is more important, so those 500ms outliers are suddenly quite important.