Maximum SQL Queries per page [closed]

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 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..

Answer 2

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.