> 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.
[1] http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy.html
edit: formatting
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.