how many db queries per page is 'a lot'

Anon

So this is a question I've been wondering about in the design of my site - how many db queries per page does it take before one can say 'it's a lot'?

Of course this is related to bogging down a server with many queries, esp if that page gets many thousand calls per day...

I've put a lot of time and effort into designing a php script which will serve a few thousand web pages (products). The database is finally in good shape with everything slimmed down and streamlined. I'm now coding the pages that will serve the dbase and on a rough estimate at present, there's going to be about 20 queries per web page served.

It got me thinking because in the header alone there are 5 queries (ok just mysql_num_rows queries) just to check the url is good (to redirect, correct any user url input errors, not to check my links are good!)

Is a page that serves 20 mysql queries going to inevitably crash a sql server?

Not looking for any fixme's but just opinions as if a general opinion is bad, I'll seriously rethink the coding...

Anon

i would never use so many queries per page! try to build small include files and parts of code you can use on such pages and pass db queries.

for example - on the page you have a dropdown with values from tableX. some people will always read values from the table and build a dropdown, instead of make option in administration "build dropdown" which created a txt file with dropdown values which you later include on the page. 1 query less... and so on.

Anon

as always on these pages - super fast response

many thanks!
actually, I like your idea a lot, and one that hadn't crossed my mind - of course, file queries are going to be much quicker than db queries, although a little more code.

Wouldn't this cause some problems though - if I vaguely recollect, opening a file for reading locks that file until finished reading (or is that file writing?)? If I'm wrong, then I will certainly go that way, well for listing all the departments, categories etc... (not for the products though, as this is too much and too dynamic)

thanks
Andy

Anon

i havent experienced any problems so far, works super fast. i believe apache/system? is caching files so it wont actually read it from the disk... works really well, try it.

rrijnders

It depends on many factors:

Is it a dedicated or virtual server?

Is the DBMS on the same server or a dedicated box?

How much horsepower is in these boxes?

I have entirely dynamic sites, ones that retrieve every aspect of each page from a DB. With these OO designs, that means 20+ DB queries per page view, minimum. Some of these sites get 5-6 thousand page views per day with no problem. Most of them run on a shared server, too.

I don't know about MySQL, but Apache + PostgreSQL + PHP handles it like a champ with everything, including POP/SMTP all on the same box.

My recommendation, concentrate on a good, maintainable design that meets the application requirements. You can always seperate the DBMS to it's own server if necessary and horsepower is cheep. A good, maintainable design will save you far more money in the long run.

my $0.02

-- Rich

Anon

well, that gave realtime info to my problem -

when implemented, site+dbase will reside on dedicated server (~1ghz+/512ram), dns on 3 lame dogs and mail on some concoction yet to be determined (but resides at the moment on an old coloc server serving many mail domains)

when things kick in, dbase will move to similar but separate server

server will serve nothing else but web/mysql

(although no guru, I'm too long in the teeth to move over to postgresql)

Still, all power aside, it's best to design the code to run at its most slick (I'm a bit of a lazy arse like that - once implemented, the least amount of recoding the better!)

Anon

am looking into it -
will post back benchmarks if/when I implement the code