> 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.
As you described it, the query/caching strategy sounds too complicated.
mysql proxy might help you out, or mysql triggers, or plenty of easy indexing strategies.
i agree, caching strategies are not easy, but that is not the fault of databases. IMHO keeping your data layers separate is best.
if you have a multi-site installation, set some kind of prefix within the app that makes sense to you for each application, before it goes into whatever cache.
this will work for you whether it is shared memory, memcached, or some kinda file situation.
> As you described it, the query/caching strategy sounds too complicated.
But that was his whole point - needlessly complicating an otherwise simple setup to get around limitations in the DB engine.
> mysql proxy might help you out, or mysql triggers, or plenty of easy indexing strategies.
I genuinely fail to see how mysql-proxy [1] (which has a sleuth of long-standing, unfixed, problems on its own), or triggers would (elegantly) help in this situation.
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.