drmovi
September 20, 2012, 3:23pm
1
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 ?
drmovi
September 20, 2012, 3:34pm
3
Two things here:
the dependency may not use DB
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".
drmovi
September 20, 2012, 4:03pm
5
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.
drmovi
September 20, 2012, 9:16pm
9
i suggest to read this fantastic book High Performance MySQL: Optimization, Backups, and Replication specially optimizing query section
softark
September 20, 2012, 11:01pm
10
drmovi:
$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();
Slightly off topic, but one problem with this sample code is that it doesn’t support the deletion.
