MySQL: How many queries per page is too many?

Question

I'm new to MySQL and something that's quickly becoming obvious to me is that it feels considerably easier to create several database queries per page as opposed to a few of them.... but I don't really have a feel for how many queries might be too many, or at what point I should invest more precious time to combining queries, spending time figuring out clever joins, etc.

I'm therefore wondering if there are some kind of "mental benchmarks" experienced folks here use with regard to number of queries per page, and if so, how many might be too many?

I understand that the correct answer in any context is related to what's needed to satisfy an application's functional requirements. However, on projects where client requirements may be flexible or not properly set, or on projects where you as the developer have full control (e.g. sites you develop for yourself), you may be able to negotiate between functionality and performance... basically, to just cut trivial features if coding requirements impact performance and you're unable to optimise it any further.

I would appreciate any views on this.

Thanks

Answer 1

There's no set number, "page" is arbitrary enough - one could be doing one database task while another could have 2 dozen widgets each with their own task.

One good rule of thumb though: the moment you put a SELECT inside a loop that's processing rows of another SELECT, stop. It might seem fast enough early on, but data tends to grow and those nested loops will grow exponentially with it, so expect it to become a bottleneck at some point. Even if the single query ends up being significantly slower, you'll be better off in the long run (and there's always stored procs, query caching, etc).

Answer 2

It depends how often the page is used, the latency between the app server and database server, and a lot of other factors.

For a page which only displays data, my gut feeling is that 100 is too many. However, there are some cases where that may be acceptable.

In practice you should only optimise where necessary, which means you optimise the pages that people use the most, and ignore the minor ones.

In particular, the pages are not available to the public and the (few) authorised users hardly ever use them, there is no incentive to make them faster.

If there is a real performance problem which you believe comes from having too many queries, enable the general query log (which may make performance worse, I'm afraid) and analyse the most common queries with a view to eliminating them.

You might find that there are some "low hanging fruits" - simple queries on rarely changing data which are called on most popular pages, which you can easily eliminate (for example, have your app server fetch the data on a cron job into a local file and read it from there). Or even "lower hanging fruits" like queries which are completely unnecessary.

The difficulty with trying to combine multiple queries is that it tends to go against code-reuse and code maintainability, so you should only do it if it is ABSOLUTELY necessary; it doesn't sound like you have enough data yet to make that determination.