The framework has a neat way of caching query results.
$rows = Yii::app()->db->cache(1000, $dependency)->createCommand($sql)->queryAll();
I have 2 concerns about the way it is implemented.
1) The username is added to the cachekey
(code from queryInternal)
This is unnecessary: either this is a query that is specific for the current user and then the parameters passed to the query will contain something specific for the current user, or otherwise it is a general query and it can be cached for all users.
In either case the username should not be added to the cachekey.
Any query that handles information that surmounts the user can not be cached efficiently.
An example: suppose a homepage shows something like the geographical distribution of your userbase, their post count, ranking, etcetera. That is a typical costly query that can be cached. But because the username is added to the cachekey, the query will be executed per user, wasting time and cache space.
2) The "duration" parameter in db->cache() behaves differently from the "expire" parameter in cache->set().
For some reason it is obligatory to have an expiration time in db->cache() while it is perfectly possible using cache->set() to have only a dependency, without expiration.
The documentation of db->cache() says: if duration is 0, the caching will be disabled. Why would a developer ever write db->cache(0, $dependency)->createCommand(…) ? Surely you would then just write db->createCommand(…).
In my opinion, the correct way to interprete db->cache(0, $dependency) is: no expiration. Exactly like cache->set() interpretes it.
Having a query cache that has no expiration and only depends on a dependency object is not possible (or you have to pass some idiotic high value in $duration).