max mysql queries per page?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
crabyars
Forum Commoner
Posts: 37
Joined: Thu Jun 17, 2004 8:24 pm

max mysql queries per page?

Post by crabyars »

Hi, I've been working on a couple new sites and was wondering - on the "average" database driven site, how many mysql queries per page is considered maximum?

At the moment I have about 8 per page, and was wondering if I should optimize (but that would make for some seriously complex query statements)

I imagine a postnuke style site makes a good 20, or 30. (and most CMS's are a bit too slow for my taste)

Thanks!
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post by Maugrim_The_Reaper »

Depends on too many variables to say. I try to keep it under 5 wherever possible - but it depends a lot on the structure of the underlying data. Do your tables comprehensibly contain the data you need, or is it spread across two or more tables? How are those tables related?

There are numerous ways of creating queries using joins and similar methods - would be worth reading up before doing anything. Some of my own creations run quite a lot more than 20 queries - planning and experience counter that sort of ill-thought out result...:)
crabyars
Forum Commoner
Posts: 37
Joined: Thu Jun 17, 2004 8:24 pm

Post by crabyars »

hmmm.. sounds like i should lookat making some super mega queries and cut down the number of DB accesses eh?

Thanks for your reply!
rhaynes
Forum Newbie
Posts: 18
Joined: Mon Nov 08, 2004 8:32 am

number of sql queries

Post by rhaynes »

Hi, is the issue of the number of queries just response time related ??? I a new php/postgreql fan. I have an html page which produces a form which allows me to update hundreds of records . The page load the entries from the database, I update the entries that need changing and hit submit and then the database is updated by using a loop over each row in the form. So that page is doing hundreds of update queries when I hit the "submit" button.

R Haynes
cggreer
Forum Newbie
Posts: 14
Joined: Mon Jul 21, 2003 6:39 am

Post by cggreer »

Potentially off-topic, but you can improve the performance of pages that execute many SQL statements by using persistent connections. Note that this isn't always possible depending on the server setup, and isn't guaranteed to solve all performance problems, but is a more efficient use of resources.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

Iterating over hundreds of rows and updating will probably reach a point quickly where you get a script timeout. Is there any possible way you can do the updates in one query? i.e. are you updating the same column with the same value for each?

fv
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post by Maugrim_The_Reaper »

Planning - I did mention it :)

If all you're doing is a an update form useable by, I assume, only a handful of people - then it's hardly worth the effort of optimising.

Better planning is required in a situation where you have many users all acessing your site at once. At that stage, such loops will prove horribly slow. Hard to give suggestions without a specific example - but the fewer queries, the better. Might mean using joins, or some other method - or simply organising your data in a more centralised form - use 1 table rather than 2 if it's quicker.

As with most things there's a tradeoff between aggregating data and normalising your database.

Persistent connections in a heavy use scenario are a no go area. They sound good on the surface but in many environments they do slow things down by consuming mysql connections which are limited in number. Might be worth while if you can configure mysql yourself but I doubt it...
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post by xisle »

Eight could be too many, but it really depends on the hardware setup.
We use content switches with compression for load balancing, they access redundant web servers which in turn access dedicated mysql servers with striped drive arrays. But I still use joins as frequently as possible because too many other factors can effect page response times.
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

Though big joins can also have a detrimental effect on performance. I had one here at work that took about 4 minutes to create a report. By removing the joins and creating three statements instead of one, combined with code manipulation of the results of each of the queries I reduces the output time to about 5-10 seoonds with a 4 fold increase in output.

The answer is, as has been said, that it depends. What you are after is perfomance. It some queries it might make sense to do joins to reduce the number of queries and gain performance. Other queries, due to their nature, and relationship to the page can be run separately. Experience is the best teacher here
Post Reply