YII SQL Query Optimization

mysql, yii

Solution

1) Make a list of book ids

foreach $product in Product-List
    $book_ids[$product->book_id] = $product->book_id;

Now query all Book models ( indexed by `book_id` )

$books = Book::model()->findAll(array(
    'index' => 'book_id',
    'condition' => 'book_id IN (' . implode(',', $book_ids). ')',
));

Integrate `$books` in your code, I believe you are looping through all products.

foreach $product in Product-List
    if( isset($books[$product->book_id]) )
        $model = $books[$product->book_id]

2) Another way (I am just assuming you have Product model)

in Product model add a relation to Book

public function relations() {
    .......
    'book'=>array(self::HAS_ONE, 'Book', 'book_id'),
    .......
}

While retrieving your product list, add `'with' => array('book')` condition, with any of CActiveDataProvider or CActiveRecord ...

//Example
$productList = Product::model()->findAll(array(
    'with' => array('book'),
));

foreach( $productList as $product ) {
    .......
    if( $product->book != null )
        $model = $product->book;
    ......
}

with either way you can reduce SQL queries.

Problem

I have a huge list of IDs that i need to query through a table to find if those IDs are available in the table, if yes fetch its model. Since there are few thousands of IDs this process is really slow as I'm using CActiveRecord::find() mothod ex. `$book = Book::model()->find('book_id=:book_id', array(':book_id'=>$product->book_id));` I even indexed all possible keys, still no improvement. Any suggestions to improve the execution speed? thanks in advance :)

Original source