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.