findByPk performance

Yii 1.1 General Discussion
1

Hi

Is there a way to make the findByPK() ‘faster’

I have a page where I need to do a lot of DB activities - yiidebug says 289 queries.

So I started to look at the queries to see where I could optimize and I saw that model::findByPk() uses 3 select statements to select 1 record, like this:




Querying SQL: SHOW COLUMNS FROM `ugeplaner`

in /Web/webtest/public/protected/models/Ugeplaner.php (31)

in /Web/webtest/public/protected/controllers/SlankeplanController.php (142)

in /Web/webtest/public/protected/controllers/SlankeplanController.php (135)







Querying SQL: SHOW CREATE TABLE `ugeplaner`

in /Web/webtest/public/protected/models/Ugeplaner.php (31)

in /Web/webtest/public/protected/controllers/SlankeplanController.php (142)

in /Web/webtest/public/protected/controllers/SlankeplanController.php (135)







Querying SQL: SELECT * FROM `ugeplaner` `t` WHERE `t`.`ugeplannr`=94 LIMIT 1

in /Web/webtest/public/protected/controllers/SlankeplanController.php (142)

in /Web/webtest/public/protected/controllers/SlankeplanController.php (135)

in /Web/webtest/public/index.php (13)

if the model have the getPrimaryKey() method implemented you could skip 2 statements - and I even don’t understand why there is a ‘show create table’ query

Kind regards

Steen

2

http://www.yiiframework.com/doc/guide/1.1/en/database.ar#establishing-db-connection

3

use cache scheme

http://www.yiiframework.com/doc/guide/1.1/en/topics.performance

4

None if the 2 links explain why the ‘show create table’ statement is needed.

Even with the ‘schemaCachingDuration’=>3600, 3 statements is issued

As I said I can understand why the ‘SHOW COLUMNS FROM’ statement is issued if primarykey() method on the model is NOT implemented

5

From first link:

This should work:


'schemaCachingDuration' => 3600

After adding it to your config you will have 3 queries again. On further requests you will have 1 query (since the other 2 about the meta data got cached).

6

Here is a part of my /config/main.php




'db'=>array(

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

			'emulatePrepare' => true,

			'username' => 'YYYY',

			'password' => 'XXXXXX',

			'charset' => 'utf8',

			'tablePrefix' => '',

			'schemaCachingDuration'=>3600,

		),

And it is still 3 statements :(

7

you need to add also this


'cache'=>array(

		   'class'=>'system.caching.CFileCache',

			'directoryLevel' => 1,

		),
8

You need to add the last bit because Yii needs to know where to cache the information. You can also use other caching methods of course.