CODEWITHSUNDEEP
mysqlyiiredisyii2yii-extensions
Chizuoka
Chizuoka

Reputation: 67

Yii2 Mirror a database table to redis for high speed active record query

What I am trying to do is to cache all the results in a MySQL table that seldom changes, so as to minimize calls to database and increase query speed. There are about 100k records in there.

Is there a library that can sync changes made in this table, like say when a record is updated or inserted, the redis cache will also be invalidated and updated.

I have seen one for elasticsearch, but nothing for redis.

From this page: Yii copying data from one model to another

There is this comment:

You can get all models attributes by:

$data = $model->attributes; and assign them to another model

$anotherModel = new AnotherActiveRecord();

$anotherModel->setAttributes($data);

now another model will extract whatever it can from $data

I'm curious, can a Redis cache also "mirror" the data from a database table in a similar way?

Or is this just a bad idea overall, and its better off caching the query as it comes along, or is there a better way.

Upvotes: 0

Views: 870

Answers (1)

meysam
meysam

Reputation: 1794

You can enable caching based on https://www.yiiframework.com/doc/guide/2.0/en/caching-data 

[
    'components' => [
        'cache' => [
            'class' => 'yii\redis\Cache',
            'redis' => [
                'hostname' => 'localhost',
                'port' => 6379,
                'database' => 0,
            ]
        ],
    ],
]

and then use Query Caching which natively defined on query builder level

$result = $db->cache(function ($db) {
    // the result of the SQL query will be served from the cache
    // if query caching is enabled and the query result is found in the cache

    // ... perform SQL queries here ...
});

Also you can use Cache Dependencies based on your table (some criteria like if max(updated_at) is changed or not).

// Create a dependency on updated_at field
$dependency = new yii\caching\DbDependency(['sql' => 'select max(updated_at) from my_table']);
$duration = 60;     // cache query results for 60 seconds.

$result = $db->cache(function ($db) {
    // ... perform SQL queries here ...

    return $result;
}, $duration, $dependency);

Upvotes: 0

Related Questions