How many queries in one webpage?

Question

How many queries in one webpage is good performance? If that page is home page that is viewed many times.

and how about....

$sql1 = mysql_query("SELECT * FROM a", $db1);
while($row = mysql_fetch_assoc($sql1)){
  $sql2 = mysql_query("SELECT * FROM b WHERE aid='a'", $db2);
  $a = mysql_fetch_assoc($sql2);
}

is it good? acctually I can combine $sql1 and $sql2 together by INNER JOIN but the problem is $sql1 is query data from database 1 and $sql2 is query data from database 2. and I use Parallels Plesk Panel that doesn't allow me to add same database user to multiple database.

If I use this code on my website, is it good? or anyway to do this?

Thanks...

Answer 1

Measure it.

For the specific case outlined above, I'd combine to a join if possible.

In general, multiple queries per request is pretty normal.

Many sites have tens of requests per query and they are fairly performant.

Use a load tester like Apache bench. (If you have Apache installed, type ab to see the parameters)

Answer 2

Actually you have 2 questions in 1.
A general one and a particular one.
Both has obvious answers in my opinion.

How many queries in one webpage is good performance?

There is no direct connection between number of queries and performance. Database setup, architecture and tuning is responsible for the performance.
And number of queries should be caused by database architecture only. Use as many queries as many you need. Do not reduce number of queries at any cost, only in pursue of performance.

is it good?

Does it matter if you have no choice?

And another, unspoken question:

Should I be concerned about this code snippet performance?

Should you?
Do you have any performance issues at the moment?
If not - why to worry at all? Why to worry about this particular snippet, not any other one?
If yes - you have to profile your code first.
And then build your optimization strategy based on the profiling results. It may be number of queries, it may be proper indexing, clusterization, server upgrade.
Do not blind shoot. Take sensible steps.

Answer 3

I like to keep mine under 8.

In all seriousness though, that's pretty meaningless. If hypothetically there was a reason for you to have 800 queries in a page, then you could go ahead and do it. You'll probably find that the number of queries per page will simply be dependant on what you're doing, though in normal circumstances I'd be surprised to see over 50 (though these days, it can be hard to realise just how many you're doing if you are abstracting your DB calls away).

Slow queries matter more

I used to be frustrated at a certain PHP based forum software which had 35 queries in a page and ran really slow, but that was a long time ago and I know now that the reason that particular installation ran slow had nothing to do with having 35 queries in a page. For example, only one or two of those queries took most of the time. It just had a couple of really slow queries, that were fixed by well-placed indexes.

I think that identifying and fixing slow queries should come before identifying and eliminating unnecessary queries, as it can potentially make a lot more difference.
Consider even that three fast queries might be significantly quicker than one slow query - number of queries does not necessarily relate to speed.

I have one page (which is actually kind of a test case/diagnostic tool designed to be run only by an admin) which has over 800 queries but it runs in a matter of seconds. I guess they are all really simple queries.

Try caching

There are various ways to cache parts of your application which can really cut down on the number of queries you do, without reducing functionality. Libraries like memcached make this trivially easy these days and yet run really fast. This can also help improve performance a lot more than reducing the number of queries.

If queries are really unnecessary, and the performance really is making a difference, then remove/combine them

Just consider looking for slow queries and optimizing them, or caching their results, first.