Cactiverecord And Database Indexes

Yii 1.1 General Discussion
1

Hi all,

apologies if this is a stupid question, I am very new to database work.

I have a table that contains geolocation city data. Two of the columns are ‘subdivision_code’ and ‘city_name’. I would like to improve the speed of a query that returns all city names for a given subdivision code. As far as indexes go, will setting one that includes both these fields do it?

I have tried doing this and while it does not improve the speed, I suspect that the problem has more to do with either how I have created the index, or my query.

The query I am using is as follows:




$criteria = new CDbCriteria();

$criteria->addSearchCondition("subdivision_code", $subdivisionCode);

$criteria->select = 'name';

$criteria->order = 'Name ASC';

$cities = City::model()->findAll($criteria);

Is the fact that this ‘findAll()’ returns an array of model objects have something to do with the fact it is not working?

2

CDbCriteria::addSearchCondition() generates a condition like this:


subdivision_code LIKE "%value%"

by default. Indexes are useless in this case. CDbCriteria::addColumnCondition() might be a more appropriate choice, depending on application logic.

The order of columns in the index is also important. Using indexes for ordering can be tricky (or even impossible) sometimes, the "Perfomance optimization" chapter in the documentation of your DBMS can help a lot.

There’s usually no reason to populate an array of model objects when you only need a single column value. CDbCommand::queryColumn() does the job as well, and the performance improvement can be noticeable on large resultsets.

3

Regarding the index: the query can be faster with an index that has both columns.

The last column in the index should be the one that you order on.

However, as indicated, only an exact match (using ->compare() ) will actually leverage on a standard index.