What are a lot of MySQL queries per page?

On my site some of my pages are pushing 100 queries per page load. I'm just wondering what would be considered too many MySQL queries on a page. I try to minimize query usage as much as possible but I was just wondering.

I try to keep mine down to one per page. Max 3 or 4 if it is complicated. Most of the data on your page could probably be precalculated and put into another table.

What will you do when you get 50,000 visitors a day? Will you have 100*50,000 or 5,000,000 mysql queries per day?

wow that is a lot of queries, are you using a super duper software application of something

100 queries per page load is truly horrific I've never seen a page perform that many queries. Have you looked at 'join' statements They may help. You're sooooooooooooo far over the mark it's hard to over-state.

I recently spent a day rewriting a class because it was performing 8 queries for a specific page load and that was deemed unacceptable.

Cheers, Prime ...

Have to echo what has already been said.

Its difficult as you don't give any idea of the type of queries or what kind of application but perhaps using more advanced SQL statements might reduce this load. Also maybe think about caching your results.

You can cache a single result to a text file using the serialize function and file_put_contents . Or you could cache the whole page if the page content returned is the same for each visitor.

http://www.w3schools.com/sql/sql_join.asp
http://www.w3schools.com/sql/sql_union.asp

First of all I am very thankful to all of those who replied to my question. Thanks a lot guys. I just want to clarify my question again. I'm talking about how many times I call the mysql_query() function. Not how many times the function is physically programmed into the page. Just incase you thought I meant the other way.



Yeah I use those when I can usually, like I would never have 2 queries right next to each other looping out data. In fact I have 1 query that gets data from 8 tables at once! I think the main problem though is the way my site is setup for easy programming is I have functions that usually do a lot of seperate database calls and put them into a loop of a MySQL query or something a lot. For example on my site every user has a user ID, and I have a function that gets their nickname and for the sake of easiness when I'm programming instead of joining the tables I usually just end up using the function for the nickname cause it performs a bunch of other things and so if I change something that has to do with it, then I don't have to change all 200 pages of my site. But this is just one small example and I actually try to not call that function as often as possible and it's not one of my more worst case problems. A lot of times I will need to perform entirely seperate queries inside of the result loop of another query.



Is that really effective? I mean it's kind of like storing data into a database. It seems it would take a lot of processing power too. I see how it would be useful for a ton of queries. Do you know of a class of some sort for this I could checkout? I've never seen it before on such a large scale.

The pages on my website are setup with a header and a footer with a side table. My menu is dynamic and has a lot of database calls on it's own along with some more in my main page included in all the pages. If I create a new blank page on my website with the side menu (which has an AJAX messenger on it that I made) then there is 38 queries on avg done.

But without the side table, a blank page with the menu and header and all that is about 21 queries usually. I guess it's not as bad as a hundred. But for example on my forums, on a topic that is 2 pages big, and if I'm showing 1 full page (as big as you can get), there are 186 queries! (that's probably the most on my site though)

I know that is still a lot, but my site is pretty heavy. It's not a pretty design website. I use a database class and when I'm logged in as an admin I have it print out the number of queries that have run on the page. I'm wondering if any of you guys do that? You honestly only get 8 or less queries per page? Do you physically count them or actually use a statistics script that gives you that information?

I was doing a database query everytime I called my own version of a date() function which among many other things includes getting the users GMT and DST settings and instead I called it once in my main script and saved it and it went from 62 queries on my main page to 47... And that forum topic view page went from 186 to 149 queries. Only problem is I can't do many other things as easily. I just had an idea though to track my redundant queries easily. In my query() function of my database class I'm going to have it store all queries made into an array, then if I'm logged in have it display all queries at the bottom of the page.

I'm gonna have a lot of fun doing optimization for the next few months...

hmmmm thats a ton of queries. It's gonna be hard to go thru and get rid of un-needed queries and such

lol, that flowchart is kinda hard to read. The only problem is if I did that, every time it would be NO & NO, so it would be useless because my pages are always dynamic. I'm not sure how you'd implement that. Like for example I have a memberslist page that lists users accounts from the database. Can I cache that? What if someone new joins? I'd still have to do a query every page load to see if someone joined. Which makes it pointless. Unless I'm not getting something... *yawn need sleep*

When someone new joins, a static profile page should be generated. When they update their profile, re-generate that static HTML page.

When someone new joins, re-generate pages with numbers effected by that new person joining.

When content is updated, regenerate all pages effected by that content change.

You want to avoid generating content dynamicly for the visitor as much as possible.
If a page gets updated once every 60 seconds but you have 60 people per-second looking at that page, there's potentially alot of wasted effort in generating the page dynamicly each time.

AJAX makes it easier to leave statistics like who's online & stuff that should generally be considered "live content" out of pages. A system dedicated to sessions can be queried for that type of information.

It's not easy, I'm just barely leaning on the surface here, haven't even scratched it.

The flowchart should be considered transitional.

Maybe you can provide an example of your header/menu code that has the 21 something queries.

Perhaps you're query is on the wrong side of a loop?

The 21 queries is now down to 14 queries (8 unique tables), and only a couple even have a loop and in those cases it only outputs the result, and the query is above the loop.



haha, that's a lot of code for me to post. Maybe you could provide an example of caching a dynamic content page, because I'm too stupid to figure out what you mean (slow learner ). However I assume that's a lot of code too.

Can you post your database table definitions

$userlist = mysql_cached("SELECT * FROM users",60); // Get a user list, updated every minute
print_r($userlist);

Code control can be, cached or uncached. As well, there is NO mysql database connect unless the file is not available. Only connects when the file is needed.