I’ve just moved a Magento 1.9 site using a tarball and a mysqldump of the old site to a new server having the same hardware specs as the old one, followed all instructions I could find online, saw that everything seemed to be working well and found no problems logging into and using the admin backend.
However the front-end became painfully slow (it takes 1 minute to load a complete page) so I did a ‘SHOW FULL PROCESSLIST’ on MySQL and found out that it was stuck on this query:
SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_active`, IF(at_include_in_menu.value_id > 0, at_include_in_menu.value, at_include_in_menu_default.value) AS `include_in_menu`, `core_url_rewrite`.`request_path` FROM `catalog_category_entity` AS `e`
INNER JOIN `catalog_category_entity_int` AS `at_is_active_default` ON (`at_is_active_default`.`entity_id` = `e`.`entity_id`) AND (`at_is_active_default`.`attribute_id` = '42') AND `at_is_active_default`.`store_id` = 0
LEFT JOIN `catalog_category_entity_int` AS `at_is_active` ON (`at_is_active`.`entity_id` = `e`.`entity_id`) AND (`at_is_active`.`attribute_id` = '42') AND (`at_is_active`.`store_id` = 1)
INNER JOIN `catalog_category_entity_int` AS `at_include_in_menu_default` ON (`at_include_in_menu_default`.`entity_id` = `e`.`entity_id`) AND (`at_include_in_menu_default`.`attribute_id` = '67') AND `at_include_in_menu_default`.`store_id` = 0
LEFT JOIN `catalog_category_entity_int` AS `at_include_in_menu` ON (`at_include_in_menu`.`entity_id` = `e`.`entity_id`) AND (`at_include_in_menu`.`attribute_id` = '67') AND (`at_include_in_menu`.`store_id` = 1)
LEFT JOIN `core_url_rewrite` ON (core_url_rewrite.category_id=e.entity_id) AND (core_url_rewrite.is_system=1 AND core_url_rewrite.store_id='1' AND core_url_rewrite.id_path LIKE 'category/%') WHERE (`e`.`entity_type_id` = '3') AND (`e`.`entity_id` IN('210', '211', '212', '213', '214', '206', '207', '258', '259', '294', '299', '300', '203', '204', '205', '202', '3', '77', '5', '9', '10', '11', '12', '13', '6', '14', '15', '16', '17', '18', '19', '7', '20', '21', '22', '23', '24', '25', '26', '78', '181', '8', '27', '28', '29', '30', '79', '80', '81', '82', '170', '171', '172', '173', '174', '175', '176', '177', '178', '179', '180', '182', '4', '31', '209', '42', '43', '192', '193', '32', '44', '45', '46', '47', '48', '49', '50', '51', '194', '33', '34', '35', '37', '38', '39', '52', '57', '58', '59', '60', '62', '63', '64', '65', '66', '67', '53', '54', '195', '196', '55', '68', '83', '84', '85', '86', '87', '88', '89', '90', '91', '92', '93', '94', '95', '96', '97', '98', '99', '100', '101', '102', '103', '104', '105', '106', '107', '108', '109', '110', '111', '112', '113', '114', '115', '116', '117', '118', '119', '120', '121', '122', '123', '124', '125', '198', '69', '126', '127', '128', '129', '130', '131', '132', '133', '166', '167', '168', '199', '70', '134', '136', '135', '137', '138', '139', '140', '141', '142', '71', '143', '144', '145', '146', '147', '148', '149', '150', '151', '152', '153', '154', '155', '156', '157', '169', '184', '185', '186', '187', '158', '159', '183', '160', '188', '189', '190', '255', '256', '257', '72', '161', '162', '163', '164', '165', '200', '74', '75', '76', '201', '215', '216', '221', '223', '245', '218', '224', '225', '226', '227', '228', '229', '230', '219', '231', '232', '233', '234', '235', '236', '238', '239', '220', '241', '242', '243', '244', '249', '252', '253', '260', '261', '305', '306', '307', '308', '309', '310', '311', '312', '313', '314', '315', '316', '317', '318', '319', '320', '321', '322', '323', '324', '262', '263', '264', '265', '266', '267', '268', '269', '270', '271', '272', '274', '275', '276', '277', '278', '301', '302', '303', '304', '279', '280', '282', '283', '284', '285', '286', '287', '288', '281', '289', '290', '291', '292', '293', '295', '296', '297', '298')) AND (`e`.`entity_id` NOT IN('42', '43', '51', '68', '69', '70', '72', '74', '75', '76', '83', '84', '85', '86', '87', '88', '89', '90', '91', '92', '93', '94', '95', '96', '97', '98', '99', '100', '101', '102', '103', '104', '105', '106', '107', '108', '109', '110', '111', '112', '113', '114', '115', '116', '117', '118', '119', '120', '121', '122', '123', '124', '125', '126', '127', '128', '129', '130', '131', '132', '133', '134', '135', '136', '137', '138', '139', '140', '141', '142', '143', '144', '145', '146', '147', '150', '151', '152', '153', '154', '155', '156', '157', '160', '161', '162', '163', '164', '165', '166', '167', '168', '169', '184', '185', '186', '187', '188', '189', '192', '198', '199', '200', '201', '202', '207', '209')) AND (IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) = '1') AND (IF(at_include_in_menu.value_id > 0, at_include_in_menu.value, at_include_in_menu_default.value) = '1')
which to my understanding is the query to build the category hierarchy(?) The catalog has 7,500 products, catalog_category_entity has 304 rows, core_url_rewrite WHERE is_system = 1 has 21,090 rows (from a total of 749,932 rows).
Yes I have cleared the cache and reindexed using php -f shell/indexer.php reindexall
and from the admin backend as well but the query keeps showing up for every single click that renders the front-end. What I’ve noticed is that on the old site this query never shows up so everything on the front-end was snappy.
I have also tried to set ‘Use Flat Catalog Category’ to ‘Yes’ but still got a slow query only in a different form which takes almost the same amount of time to execute:
SELECT `main_table`.`entity_id`, main_table.`name`, main_table.`path`, `main_table`.`is_active`, `main_table`.`is_anchor`, `url_rewrite`.`request_path`, `main_table`.`umm_dd_type`, `main_table`.`umm_dd_width`, `main_table`.`umm_dd_proportions`, `main_table`.`umm_dd_columns`, `main_table`.`umm_dd_blocks`, `main_table`.`umm_cat_target`, `main_table`.`umm_cat_label` FROM `catalog_category_flat_store_1` AS `main_table`
LEFT JOIN `core_url_rewrite` AS `url_rewrite` ON url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND url_rewrite.store_id = 1 AND url_rewrite.id_path LIKE 'category/%' WHERE (main_table.include_in_menu = '1') AND (main_table.is_active = '1') AND (main_table.path like '1/2/%') ORDER BY `main_table`.`position` ASC
Has anybody ever experienced such a thing before and did you find a solution to it? Any help would be greatly appreciated.