限制Magento非eav表的查询

Question

I have a custom table in my database with approximately 900 000 rows. I am running this query:

$products = Mage::getModel("similar/flipkart")->getCollection()->addFieldToFilter('title', array('like' => '%'.$query.'%')) ->setCurPage(1)
                ->setPageSize(4);

This query takes more than 12 seconds to return result. However when I run limit query on mysql table, I get result instantaneously. How do I speed up my process. When logged the query, I got this:

`SELECT main_table.* FROM flipkart_furn AS main_table WHERE (title LIKE '%chai%')` 

So basically limit did not append to it. How do i make limit gets appended to it

Answer 1

I ran direct SQL query from like this way (Although not a good answer but just a workaround):

$resource = Mage::getSingleton('core/resource');

    /**
     * Retrieve the read connection
    */
    $readConnection = $resource->getConnection('core_read');

    /**
     * Retrieve our table name
    */
    $table = $resource->getTableName('similar/flipkart');

    /**
     * Set the product ID
    */
    //$productId = 44;

    $query = 'SELECT * FROM ' . $table . ' WHERE title like "%'.$query1.'%" LIMIT 4';

    /**
     * Execute the query and store the result in $sku
     */
    $products = $readConnection->fetchAll($query);
    //$sku = $readConnection->fetchOne($query);


    /* $products = Mage::getModel("similar/flipkart")->getCollection()->addFieldToFilter('title', array('like' => '%'.$query.'%')) ->setCurPage(1)
                ->setPageSize(4);
    Mage::log((string)$products->getSelect(),null,"mylogfile1.log",true);*/
    return $products; 

I ran this from my magento model class directly. I got the result in no time