There are a few places where the number of queries can be cut down significantly. My testing indicates that even cutting everything down to just a couple of queries doesn't really change performance much as it's just a move from a lot of little, fast queries to a few slower ones. (Caching or improving the regex stuff would have a much bigger effect.) However the point has been brought up that some hosts limit the number of queries per hour, so it would be good to reduce the number we use. I also think that, in principle, we should try to use as few queries as possible.
So, if you would like to help with this bit of optimization, please post to this thread. We all understand the concepts, so don't preach, just show some SQL and PHP code.
Query Optimization (4 posts)
-
-
*BUMP*
-
Xial
Posted 5 years ago #I'd like some help with that, myself.
-
davidchait
Posted 5 years ago #Hey Matt!
Pretty coincidental -- I didn't even notice this topic way down the page before I posted my query thing.
I'm guessing we'd need to analyze a few specific sites, some specific pages, etc.
Just struck me that some of the queries for sidebars, like "last n posts", could be cached either on disk or in tables in the db and updated only when needed, such as when posts/comments are added/edited/deleted (an easy bottleneck-point to place such generation code).
In one sense, it starts to go against the flow of 'live generation' -- but I'm a coder, so I gravitate towards caching mechanisms anyway.
The comments-count thing is as good a place to start as any. I added a cache variable just to cache the results so it didn't double-query on each post, but as I mentioned, it'd seem prudent, and manageable, to keep an up-to-date comment count inside the post data itself.
Then, maybe caching the categories table once at the top -- small set of data (generally), used frequently. As the # of posts could grow immeasurably, caching post2cat isn't useful, but I also noted that maybe having single-category posts keep their category in the post data would be good.
Again, some of this really depends on the user and usage patterns. If you want snippets, more analysis, further suggestions, let me know.
=d
Topic Closed
This topic has been closed to new replies.
