How To Enable Schema Caching With Mysql Database

Yii 1.1 General Discussion
1

Hi everyone,

I’m at the end of the yii blog tutorial. Final tuning with schema caching. The tutorials code goes for schema caching with sqllite and I wondered what to choose for MySQL databases.

The given example for the configuration file main.php is as followed:


return array(

    ......

    'components'=>array(

        ......

        'cache'=>array(

            'class'=>'CDbCache',

        ),

        'db'=>array(

            'class'=>'system.db.CDbConnection',

            'connectionString'=>'sqlite:/wwwroot/blog/protected/data/blog.db',

            'schemaCachingDuration'=>3600,

        ),

    ),

);

I’ve found an russian topic, title: Integrity Constraint Violation Db Schema Cache, which was getting close in the forum search to my questions but not an answer. Furthermore, I can’t read russian text.

So what about something like this:


return array(

    ......

    'components'=>array(

        ......

        'cache'=>array(

            'class'=>'CDbCache',

        ),

        'db'=>array(

            'connectionString' => 'mysql:host=localhost;dbname=yii_tables',

            'emulatePrepare' => true,

            'schemaCachingDuration'=>3600,

            ......

        ),

    ),

);

And what are the options in the main.php for caching, generally?

2

Actually, caching DB queries using DB-based cache is kinda questionable.

I suppose you can gain some profit if you use something like memcache or memory DB engine.

3

Thank you Orey for the quick reply.

I agree with you that caching DB queries in a DB cache is not really thoughtful.

But, it’s actually not about caching DB queries. I’m looking for a nice and simple way to cache the DB schema, like it is in the yii blog tutorial.

Here is the reason, why they’ve used schema caching:

4

That’s pretty much the same, because DB schema is get by querying “SHOW FULL COLUMNS” and “SHOW CREATE TABLE”.

5

Uhm, ORey? You are aware that CDbCache is working with uncached DAO, are you?

6

Sure, but still it’s yet another DB request. Plus GC, btw.

PS. Sorry, last week I am like "oh no, not again, leave my DB alone!"

7

Ah, there’s still MySQL’s internal query cache taking care of that :lol:

But seriously: While there are certainly better options, there is technicaly nothing wrong with using CDbCache as a query and schema cache. Wouldn’t use this in production, though :rolleyes:

8

Actually, I suppose one can use memory storage engine, making DB cache lightning fast (?)

But the thing is, those who can do this also can install something like memcached.

BTW: as far as I remember, db cache table doesn’t have any indexes out of the box. Since this can eventually lead to significant performance loss, it must be taken into account.

9

Well, I think SQLite can also operate tables entirely in-memory … And CDbCache can be configure to use a differrent database than Yii::app()->db.

How very true.

10

Thanks for the help!