First, there still may be some people who have a limited number of databases and can use this feature. And there may be people who find it handy to have it all in one database, although I would never recommend it. In my humble opinion, separating the databases is always better.
Second, it may also be a security concern. Having a prefix for your database tables makes it harder for an attacker to guess the names of the tables and do stupid stuff.
OK, I have found a workaround. It is problematic only in the table, which has more than 2mil records.
We are searching for a categories, but inside this table there are a lot of rows regarding products. When I changed Magento Core file to add:
' AND url_rewrite.category_id is not null'
which helps database to pre-filter results. After that, the keys are used. If I can say that it is resolution or workaround, I don't know, but it is working.
Make a local copy of app/Core/Mage/Catalog/Helper/Category/Url/Rewrite.php and find:
public function joinTableToSelect(Varien_Db_Select $select, $storeId)
{
$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'));
return $this;
}
and change to this:
public function joinTableToSelect(Varien_Db_Select $select, $storeId)
{
$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.category_id', array('notnull' => 1)) .
$this->_connection->prepareSqlCondition(' AND url_rewrite.id_path', array('like' => 'category/%')),
array('request_path' => 'url_rewrite.request_path'));
return $this;
}
This will add another filter to query.
Best Answer
To avoid lock, you should use READ COMMITTED transaction isolation level.
It's seems that you are importing stocks and reindexing data in the same time, you can also change scheduling.
I can tell you that with this request :
You can check this Github issue for answer