>
I'm genuinely interested to learn which use cases are more dependent on query planning than caching methodologies.I'll reply to this separately, it's a good discussion to have.
My basic problem is that caching comes with a coordination cost and I prefer the originating data source to be as performant as possible.
My own use case is a small Wordpress multisite installation. Even with a relatively trivial amount of traffic and site data, it behaves abysmally on some simple requests. The linked article seems to explain why -- the query planner ignores indexes on certain kinds of joins. The same sort of joins as the Wordpress Recent Comments Widget.
Now, I can and have worked around this by using multiple layers of caching. There's the MySQL query cache, some memcache (PHP opcode caches would in theory be quicker but I've never been satisfied with their stability) and of course pumping gzipped HTML to disk for nginx to serve directly without hitting PHP or MySQL.
But like I said, caching comes with a coordination cost. One of my sites is used less like a blog and more like a chat room. Hundreds of comments per hour, every single one of which causes the query cache to be pruned of the exact query I most need to cache in the first place.
That is: I need to cache this query because they talk so much. But they talk so much that the cache is not that helpful.
What would be helpful is if MySQL was a bit smarter about using the indexes I put there in an apparently useless bit of chicken-waving.