Page 1 of 1

max mysql queries per page?

Posted: Sun Nov 21, 2004 8:33 pm
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!

Posted: Mon Nov 22, 2004 10:36 am
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...:)

Posted: Mon Nov 22, 2004 1:04 pm
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!

number of sql queries

Posted: Fri Nov 26, 2004 10:24 am
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

Posted: Fri Dec 03, 2004 3:03 am
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.

Posted: Mon Dec 06, 2004 8:16 pm
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

Posted: Tue Dec 07, 2004 9:12 am
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...

Posted: Tue Dec 07, 2004 10:45 am
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.

Posted: Tue Dec 07, 2004 12:43 pm
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