Query Cache Benefits

hi every body

caching is used for faster data access and one of it’s main uses is in decrease database hit rate

take this example found in wiki

$dependency = new CDbCacheDependency('SELECT MAX(update_time) FROM tbl_post');

$posts = Post::model()->cache(1000, $dependency)->findAll();

// relational AR query

$posts = Post::model()->cache(1000, $dependency)->with('author')->findAll();

this is an example of query cache with dependency . idea here is yii must execute dependency query every time query is requested

is that means that database dependency is not useful in accelerating cache as it need to be executed every time

you request a cached database ?

Two things here:

  1. the dependency may not use DB

  2. the dependency query can use much simpler and faster query than that being cached.

right but rule is to eliminate any load from db, notice also it’s logic to have dependency from the source why?

cause you change the source .

thanks for reply

Wiki example “SELECT MAX(update_time) FROM tbl_post” makes me wonder too, because aggregates aren’t cheap.

Well, not ANY load, just the complex queries.

Here’s how I see it.

Update_time does not come from nowhere, obviously there is some code that changes it (let it be beforeSave trigger)

So we can add a couple of lines and set some kind of marker, showing that the table has changed. It can be lightweight table, file, memcache and so on.

Then we execute some fat query on db and store the results in cache, so second query will be served from cache if nothing has changed.

And, btw, "select *" take much more time than "select one_field".

yes one should use query caching for fatty queries only

Hm, MAX() is not really expensive if it’s being applied on an indexed field. Same goes for COUNT(). Perform the query with EXPLAIN EXTENDED if you’re unsure.

Have you ever tried to execute SELECT COUNT on InnoDB table with 1 000 000 rows?

Haven’t had the need to. But between 250,000 and 300,000 has never been a problem.

i suggest to read this fantastic book High Performance MySQL: Optimization, Backups, and Replication specially optimizing query section

Slightly off topic, but one problem with this sample code is that it doesn’t support the deletion.

Second that.