How to add "use index for join" in left join magento1.9

[psaini]

I have two queries,

1.

{    $select = $this->_getReadAdapter()->select()->from(array('main_table' => $this->getMainStoreTable($category->getStoreId())), array('main_table.entity_id', 'main_table.name'))->where('main_table.entity_id IN (?)', array_reverse(explode(',', $category->getPathInStore())));
    if ($isActive) {        $select->where('main_table.is_active = ?', '1');
    }    $select->order('main_table.path ASC');
}

And then 2:

{    $select->joinLeft(array('url_rewrite' => $this->_resource->getTableName('core/url_rewrite')), 'url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND ' .$this->_connection->quoteInto('url_rewrite.store_id = ? AND ', (int)$storeId) .$this->_connection->prepareSqlCondition('url_rewrite.id_path', array('like' => 'category/%')), array('request_path' => 'url_rewrite.request_path'));

Now in this join, I want to add use index for join(index). So is it possible to form such queries in Magento some how?

[Damian Culotta]

Hi @psaini,

Maybe you can try with plain SQL? This thread explains how to perform plain queries: https://magento.stackexchange.com/questions/61796/magento-1-sql-queries

The main difference should be that you won't get a collection object but an array.