Maximum SQL Queries per page

Question

This question probably has no definite answer, so if it's considered subjective and, let's say bad, please feel free to close it.

Basically I am developing a pretty big web application (PHP), and it uses CakePHP. Right now it's under development and some things in the Database are really complex therefore lots of queries need to be made in order to display the pages (around 7-25 queries).

Since this is probably going to be a large-scale one, I would like to know what's the maximum, that is, sort-of a point that indicates that "You're probably doing something wrong and should optimize", number of SQL queries that should be done per page. I have a very simple caching system set up right now which reduces the queries ran by a single user to around 5 for 15 seconds.

Is running 25 queries a lot? Should I stop the development (I have plenty of time) for a while and refactor the code, remove SQL Queries that are not used, and dedicate time to improve performance on this part?

This probably sounds a little confusing, so resumed: Is there a de facto maximum for the number of queries ran per page that does not hammer a server (that is, a shared hosting environment)?

Thanks.

Answer 1

The total number of hits to your database is also proportional to the number of page views. So if page views * queries per page is more than the capacity of your database, then you've got a problem. Obviously traffic varies very widely from site to site, as does database capacity, so there really can't be a number to aim for.

Answer 2

Having 1 for general settings, 3 for navigation, and 4 for content gives a decent total of 8 for an average page. Heavy pages could probably have 10 or so for the content. But that's still 14 total, quite a lot fewer than 25.

Answer 3

As recursive mentioned, there is no magic number. Far too many variables. Such as your database server specs. Clearly the better the server the more queries you can run.

Answer 4

A possibly easier, and almost as effective method of optimization would be to batch queries where it is possible. This way you're making fewer round-trips to the database server while keeping your existing logic (mostly) intact.

Answer 5

The most important thing is not the number, but the expense of the query...

Running 100 SELECT name FROM foo WHERE id = 1 LIMIT 1 will be a whole lot better than running 1 of the following:

SELECT *
    FROM foo AS a
    JOIN bar AS b
    JOIN car AS c
    WHERE a.col LIKE '%f%' OR b.col LIKE '%b%' OR c.col LIKE '%b%'

So don't fret the number unless it's absurd (Over 100 is high. Several thousand are absurd)... Don't forget that you can enable MySQL's Query Cache... So even if you are hitting a lot of queries per second, as long as there are not a ton of updates, most of them will be directly cache results..