max mysql queries per page?
Moderator: General Moderators
max mysql queries per page?
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!
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!
- Maugrim_The_Reaper
- DevNet Master
- Posts: 2704
- Joined: Tue Nov 02, 2004 5:43 am
- Location: Ireland
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...
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...
number of sql queries
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
R Haynes
-
fractalvibes
- Forum Contributor
- Posts: 335
- Joined: Thu Sep 26, 2002 6:14 pm
- Location: Waco, Texas
- Maugrim_The_Reaper
- DevNet Master
- Posts: 2704
- Joined: Tue Nov 02, 2004 5:43 am
- Location: Ireland
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...
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...
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.
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.
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
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