Removing quotes from an INT column allows MySQL to use the index and this can reduce rows scanned by 99.997 percent. Example from Magento 1.12 on a production database; the query takes nearly a second to execute:
mysql> explain DELETE FROM `catalog_product_index_price` WHERE entity_id IN('433284', 433283)\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: catalog_product_index_price
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 773625
Extra: Using where
1 row in set (0.00 sec)This query is much faster:
mysql> explain DELETE FROM `catalog_product_index_price` WHERE entity_id IN(433284, 433283)\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: catalog_product_index_price
type: range
possible_keys: PRIMARY,IDX_CATALOGPRODUCTINDEXPRICE_ENTITYID
key: PRIMARY
key_len: 4
ref: NULL
rows: 19
Extra: Using where
1 row in set (0.00 sec)Incorrectly quoting both INT values produces the same result:
mysql> explain DELETE FROM `catalog_product_index_price` WHERE entity_id IN('433284', '433283')\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: catalog_product_index_price
type: range
possible_keys: PRIMARY,IDX_CATALOGPRODUCTINDEXPRICE_ENTITYID
key: PRIMARY
key_len: 4
ref: NULL
rows: 19
Extra: Using where
1 row in set (0.00 sec)
Ran into this today - it's locking up one of our customer's sites.
In class
Mage_Catalog_Model_Resource_Product_Indexer_Eav_Abstract
find
public function reindexEntities($processIds)
and add
//turn all the items into ints
$processIds = array_map(intval,$processIds);
before
$adapter->beginTransaction();