Home
If you ever bothered to check why does reindexing of your Magento EE database takes so long you may find something like this among the slowest MySQL queries:
DELETE `index_price` FROM `catalog_product_index_price` AS `index_price`
LEFT JOIN `catalog_product_index_price_tmp` AS `ip_tmp` ON index_price.entity_id = ip_tmp.entity_id AND index_price.website_id = ip_tmp.website_id WHERE (ip_tmp.entity_id IS NULL) AND (index_price.entity_id IN('147858', '303525', '303524', '303508', '107476', '352811', '352810', '352809', '352808', '303376', '303354', '303352', '303351', '303350', '303344', '352763', '352760', '352733', '303338', '303331', '303319', '303315', '303314', '303313', '303312', '303231', '303200', '303180', '303179', '303178', '303168', '303165', '303164', '303163', '303161', '303160', '303159', '303158', '303157', '303156', '303135', '302662', '302661', '302660', '302659', '302658', '302647', '302587', '302586', '302517', '302512', '352423', '302503', '302501', '302498', '352409', '302496', '302495', '302494', '302493', '302492', '302491', '352367', '352362', '352351', '352350', '352349', '352348', '352321', '352320', '302482', '302474', '352304', '352296', '352282', '302461', '302460', '302459', '302458', '302457', '302456', '302454', '302451', '135970', '135969', '135968', '135966', '302447', '352223', '302440', '302434', '302433', '302431', '302429', '302418', '104061', '104056', '302412', '302409', '206350', '206349', '206346', '352139', '302403', '302402', '302401', '352067', '352018', '302378', '302377', '207540', '302361', '302360', '302356', '302347', '302346', '302345', '302344', '302342', '249460', '302292', '302230', '302218', '302196', '302193', '302191', '302189', '302185', '302183', '351629', '351628', '351627', '302160', '302159', '302158', '351587', '351586', '351585', '237556', '237555', '351546', '351508', '351507', '237536', '237532', '351429', '302122', '302099', '302089', '302088', '302085', '302084', '302081', '351268', '348287', '351253', '351225', '351217', '302039', '329761', '302024', '302023', '302022', '302017', '302016', '302015', '302013', '302012', '302009', '302008', '302007', '302006', '302005', '302002', '302001', '302000', '301996', '301995', '301994', '301992', '351120', '301945', '301944', '301943', '301933', '351070', '301883', '301882', '301881', '301802', '301801', '301800', '301799', '301798', '301793', '301792', '301786', '301785', '301782', '301781', '301780', '301779', '301756', '301755', '301754', '301731', '301722', '301721', '301719', '301636', '301633', '301531', '350951', '350950', '350949', '350948', '350943', '350930', '350917', '350916', '350884', '301420', '301417', '248343', '248342', '248341', '350851', '208180', '301396', '301395', '301393', '350838', '350818', '350721', '350700', '350676', '350674', '350673', '350671', '350664', '350663', '350565', '301335', '350448', '350446', '350419', '301172', '350291', '350282', '237450', '237385', '350081', '350080', '350079', '350078', '349966', '349962', '300373', '300372', '300371', '300231', '300230', '300229', '300228', '300218', '300185', '300182', '300181', '300180', '300179', '206154', '206153', '300106', '300103', '300094', '300092', '300085', '300083', '300082', '300065', '300064', '300060', '300059', '300058', '349776', '349773', '299982', '299981', '299980', '349770', '349767', '349755', '349754', '299972', '299907', '299891', '299883', '299878', '299877', '299876', '299875', '299870', '299867', '299816', '299785', '349706', '349694', '299688', '299687', '299681', '299680', '299679', '299640', '299639', '299638', '299637', '299636', '299559', '299558', '299553', '299552', '299551', '299549', '299543', '299400', '299399', '299396', '299392', '299328', '299327', '299326', '299325', '299324', '299323', '299286', '299225', '299205', '299203', '299202', '299201', '299200', '349417', '349416', '349415', '349374', '349356', '349355', '349354', '349353', '349346', '349332', '349266', '349254', '349223', '349171', '349137', '349136', '349135', '349103', '349071', '299172', '349001', '348996', '299105', '298978', '348980', '348959', '298922', '348897', '348890', '325810', '246937', '298726', '298725', '135353', '237165', '364703', '325360', '325356', '325355', '341594', '341592', '341589', '341368', '341367', '341366', '341350', '341348', '341347', '341346', '341314', '341273', '341272', '341082', '341039', '340949', '340931', '340930', '340929', '340898', '340891', '340876', '340875', '346883', '346684', '346602', '346601', '346600', '346599', '346598', '346593', '346588', '346407', '346370', '346271', '346270', '346269', '346169', '346167', '346050', '346037', '346036', '345992', '345970', '345969', '345923', '345922', '345917', '345916', '345887', '345815', '345814', '345779', '345778', '345690', '345688', '345687', '345680', '340364', '340363', '340361', '340357', '340355', '340354', '340324', '340323', '340322', '340321', '340320', '340318', '340272', '340239', '340238', '340236', '340073', '340041', '340037', '339998', '339997', '339995', '339994', '344367', '344299', '339926', '339603', '339600', '339544', '339455', '339407', '339317', '285889', '285874', '339046', '285808', '285617', '285603', '285595', '285531', '285530', '285529', '285528', '285527', '285526', '285525', '285520', '285519', '285518', '285517', '285515', '285496', '285455', '285261', '285258', '285089', '284746', '284743', '284740', '284671', '284582', '284557', 104055, 107472, 135349, 135965, 147853, 206152, 206345, 207539, 208176, 237161, 237381, 237445, 237529, 237535, 237553, 248339, 284548, 284578, 284668, 284734, 285084, 285253, 285449, 285494, 285509, 285524, 285584, 285599, 285614, 285802, 285866, 285881, 298722, 298918, 298977, 299101, 299197, 299204, 299222, 299282, 299322, 299389, 299395, 299542, 299548, 299555, 299635, 299677, 299683, 299782, 299813, 299866, 299873, 299880, 299887, 299906, 299971, 299978, 300056, 300063, 300077, 300084, 300090, 300102, 300178, 300184, 300214, 300226, 300369, 301170, 301332, 301392, 301416, 301529, 301631, 301718, 301725, 301752, 301776, 301783, 301790, 301797, 301879, 301928, 301942, 301991, 301997, 302004, 302011, 302018, 302037, 302079, 302086, 302093, 302121, 302155, 302181, 302188, 302195, 302215, 302229, 302288, 302341, 302355, 302375, 302399, 302405, 302411, 302417, 302424, 302430, 302436, 302442, 302448, 302455, 302469, 302476, 302490, 302497, 302511, 302581, 302645, 302657, 303134, 303155, 303162, 303176, 303197, 303228, 303311, 303317, 303330, 303336, 303343, 303349, 303373, 303504, 303522, 325343, 325806, 329754, 339034, 339303, 339393, 339453, 339536, 339599, 339919, 339988, 340036, 340062, 340225, 340266, 340315, 340352, 340872, 340887, 340917, 340947, 341034, 341077, 341271, 341311, 341339, 341354, 341585, 344298, 344357, 345679, 345685, 345775, 345811, 345883, 345913, 345919, 345967, 345991, 346033, 346045, 346165, 346267, 346369, 346405, 346585, 346591, 346597, 346681, 346879, 348285, 348887, 348894, 348955, 348975, 348995, 349069, 349100, 349134, 349169, 349218, 349253, 349260, 349327, 349345, 349351, 349369, 349413, 349693, 349705, 349751, 349765, 349772, 349961, 350077, 350278, 350290, 350418, 350444, 350563, 350660, 350666, 350672, 350696, 350720, 350816, 350834, 350846, 350883, 350911, 350925, 350939, 350946, 351065, 351117, 351216, 351222, 351252, 351264, 351427, 351502, 351541, 351583, 351626, 352013, 352066, 352138, 352217, 352277, 352291, 352298, 352319, 352347, 352361, 352403, 352417, 352732, 352759, 352807, 364707));
This is a cleanup query which is generated in Enterprise_Catalog_Model_Index_Action_Product_Price_Abstract::_syncData. The list of entity IDs is passed to the _syncData method as a single parameter. This list is initially generated at Enterprise_Index_Model_Action_Abstract::_selectChangedIds, then split into chunks (default of 500 entries), passed to Enterprise_Catalog_Model_Index_Action_Product_Price_Refresh_Changelog::_reindex and then to _syncData.
The problem happens in _reindex method. There Magento gets parents of simple products and adds them to the end of an array (lines 92-109). The problem is that fetchAll which is used for for getting initial bunch is returning the array of strings and then _reindex is adding integer IDs of parents. So the result array contains 500 strings and some integers. And this is then passed to _syncData method. You can see in the query above that some of entity IDs in WHERE clause are quoted and some are not.
This is an issue for MySQL as passing mixture of strings and integers to IN() construction makes query about 100 times slower then having just strings or just integers as MySQL can't use its indices anymore. Even if you try to run 2 following queries:
SELECT SQL_NO_CACHE * FROM `catalog_product_index_price` WHERE entity_id IN('295728', 81096);
and
SELECT SQL_NO_CACHE * FROM `catalog_product_index_price` WHERE entity_id IN('295728', '81096');
The first one will take about 130ms and the second just 1.2ms.
I guess in order to reproduce the issue you need a DB with a decent number of products (I have ~250k).
The example above and all class names are referencing to price indexer but in fact categories, products and stock indexers are also affected. The Sandfox_Index module fixes this bug by simply converting the result array of entity IDs to integers. The graph bellow shows how execution time drops after fixing the issue.
