I’ll stipulate something up front here that we’ll just dismiss right away: Our templates are not (yet) built efficiently (from an EE perspective, anyway), and as such likely cause far more DB queries than they would if we rebuilt them. Long term, we will rebuild them. Short term, we will not.
I’ll also mention that over the last 10+ years we have always found that reading files from the web server is many orders of magnitude faster than reading the same data from MySQL, even if it’s in the latter’s query cache. Perhaps we just know how to make our web server run efficiently, perhaps it’s something else, but that’s what we’re working with.
Given that… we are finding that each article page is responsible for approximately 50 queries to the database. That is (to me) a ridiculously high number, and when we suddenly start serving at a rate of 20,000 pages per hour, MySQL chokes. Implementing Solspace’s static page caching module works, but that’s a very manual (and imperfect) process.
We are investigating some things about EE’s SQL Query Caching, and given all of the above it seems like this is a very GOOD idea for us, though everyone here seems to suggest it is generally a bad idea.
I’m curious as to why that is? Is there something about it that is risky? Or is it just that, in general, most folks believe that it’s more efficient to read from MySQL than it is from local files?
Thanks!