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
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
The short answer: You can't, really! The long answer: You are dealing with Magento with a huge EAV model in the background. Depending on the item count in your database it just needs time to fetch the data, very likely that Magento join some tables which makes this even longer.
One way could be to not use like (fuzzy) but to use = (sharp).
$products = Mage::getModel("similar/flipkart")
->getCollection()
->addFieldToFilter('title', $query)
->setCurPage(1)
->setPageSize(4);
like searches are really performance expensive.
If you can't go for = instead of like you maybe ramp up your server and optimize your database. Here is a superb article "Magento on Steroids" on how to improve database performance.
Also I would suggest a database maintenance as described in the article by Nexcess.
Limit just reduce the amount of results you get back - normally that also makes are query faster! Furthermore a query run directly on the server (with mysql workbench) is always faster that a query that need to be processed through an ORM, and Magento uses its own ORM.