When running SELECT queries it seems as if Yii is often performing each one twice. The first is a COUNT() and the second is the actual query.
What is causing this? It seems terribly inefficient.
In a related note, why does Yii perform a SHOW COLUMNS FROM and SHOW CREATE TABLE so often? Doesn't setting up a relation within the Model tell Yii enough about the schema?
I assume you are using active records a lot in conjunction with listing widgets such as CGridView and CListView.
What is causing this? It seems terribly inefficient.
Well, in order for the pagination to work in CListView and CGridView, the assigned CActiveDataProvider (or actually any data provider) needs to fetch the total item count. This won't work with the result set which usually has a LIMIT clause applied. Hence, an additional COUNT() is performed to retrieve said number.
In a related note, why does Yii perform a SHOW COLUMNS FROM and SHOW CREATE TABLE so often? Doesn't setting up a relation within the Model tell Yii enough about the schema?
No. Yii does far more than managing related models. Part of the AR abstraction layer is also to determine which fields are available in a table and hence can be accessed on a model representing a table row. However, you don't have to live with this as schemata can be cached conveniently. To do so, follow these steps:
CApcCache in your protected/config/main.php in the components stanza.Change the configuration of your db component so it contains the following lines:
'schemaCacheId'=>'cache', // This is the name of the cache component you
// configured in step 1. It's also the default value.
'schemaCacheDuration'=>3600, // Cache table schemata for an hour.
// Set this higher if you like.
A word of advice; don't do this in your development environment: If your database design changes, AR models might not reflect this due to stale caches.