a czy ktoś zna radę czemu mam tak dużych wolnych zapytań do bazy danych? Właściciel serwera mówi mi abym coś z tym zrobił. Strona działa bardzo wolno. Poniżej informację od dostawcy usług:
Witam,
obserwujemy dużą ilość wolnych zapytań do serwera baz danych, prosimy rozwiązać problem, niewielki wycinek poniżej:
# Time: 2016-08-24T21:30:27.125523Z
# User@Host: UŻYTKOWNIK@NAZWABD @ SERWER [85.194.242.213] Id: 16256311
# Query_time: 8.054674 Lock_time: 0.000568 Rows_sent: 154 Rows_examined: 9096051
SET timestamp=1472074227;
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('3', '145', '5', '4', '146', '6', '7', '64', '63', '57', '98', '97', '8', '32', '13', '72', '79', '143', '11', '24', '78', '10', '9', '99', '101', '100', '115', '31', '155', '157', '156', '118', '109', '12', '133', '134', '135', '136', '137', '138', '139', '148', '103', '14', '25', '119', '120', '121', '122', '123', '124', '21', '22', '28', '104', '108', '107', '144', '147', '149', '158', '159', '160', '161', '162', '163', '164', '165', '166', '167', '168', '169', '170', '171', '179', '172', '173', '174', '175', '176', '177', '178', '20', '36', '38', '39', '58', '59', '60', '61', '62', '41', '37', '40', '16', '75', '33', '34', '35', '74', '102', '73', '96', '80', '81', '77', '142', '113', '110', '76', '116', '193', '196', '195', '194', '152', '184', '180', '183', '182', '181', '185', '153', '23', '150', '15', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '65', '111', '112', '125', '126', '127', '128', '129', '130', '131', '140', '141', '132', '95', '27', '82', '84', '85', '86', '88', '83', '87', '89', '90', '91', '92', '93', '19', '17', '26', '18', '67', '66', '71', '68', '69', '70', '186', '189', '190', '191', '192', '187', '188', '30', '55', '56', '114', '197', '154', '29')) AND (`e`.`entity_id` NOT IN('19', '21', '22', '23', '29', '70', '82', '89', '92', '93', '111', '112', '114', '120', '131', '154', '158', '159', '160', '161', '162', '164', '168', '169', '170', '171', '172', '173', '174', '175', '176', '179', '193', '194', '194', '195', '195', '196', '196')) 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');
# Time: 2016-08-24T21:30:37.964148Z
# User@Host: UŻYTKOWNIK@NAZWABD @ SERWER [85.194.242.213] Id: 16256638
# Query_time: 7.870083 Lock_time: 0.000543 Rows_sent: 154 Rows_examined: 9096051
SET timestamp=1472074237;
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('3', '145', '5', '4', '146', '6', '7', '64', '63', '57', '98', '97', '8', '32', '13', '72', '79', '143', '11', '24', '78', '10', '9', '99', '101', '100', '115', '31', '155', '157', '156', '118', '109', '12', '133', '134', '135', '136', '137', '138', '139', '148', '103', '14', '25', '119', '120', '121', '122', '123', '124', '21', '22', '28', '104', '108', '107', '144', '147', '149', '158', '159', '160', '161', '162', '163', '164', '165', '166', '167', '168', '169', '170', '171', '179', '172', '173', '174', '175', '176', '177', '178', '20', '36', '38', '39', '58', '59', '60', '61', '62', '41', '37', '40', '16', '75', '33', '34', '35', '74', '102', '73', '96', '80', '81', '77', '142', '113', '110', '76', '116', '193', '196', '195', '194', '152', '184', '180', '183', '182', '181', '185', '153', '23', '150', '15', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '65', '111', '112', '125', '126', '127', '128', '129', '130', '131', '140', '141', '132', '95', '27', '82', '84', '85', '86', '88', '83', '87', '89', '90', '91', '92', '93', '19', '17', '26', '18', '67', '66', '71', '68', '69', '70', '186', '189', '190', '191', '192', '187', '188', '30', '55', '56', '114', '197', '154', '29')) AND (`e`.`entity_id` NOT IN('19', '21', '22', '23', '29', '70', '82', '89', '92', '93', '111', '112', '114', '120', '131', '154', '158', '159', '160', '161', '162', '164', '168', '169', '170', '171', '172', '173', '174', '175', '176', '179', '193', '194', '194', '195', '195', '196', '196')) 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');
# Time: 2016-08-24T21:30:48.183008Z
# User@Host: UŻYTKOWNIK@NAZWABD @ SERWER [85.194.242.213] Id: 16256817
# Query_time: 7.724487 Lock_time: 0.000502 Rows_sent: 154 Rows_examined: 9096051
SET timestamp=1472074248;
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('3', '145', '5', '4', '146', '6', '7', '64', '63', '57', '98', '97', '8', '32', '13', '72', '79', '143', '11', '24', '78', '10', '9', '99', '101', '100', '115', '31', '155', '157', '156', '118', '109', '12', '133', '134', '135', '136', '137', '138', '139', '148', '103', '14', '25', '119', '120', '121', '122', '123', '124', '21', '22', '28', '104', '108', '107', '144', '147', '149', '158', '159', '160', '161', '162', '163', '164', '165', '166', '167', '168', '169', '170', '171', '179', '172', '173', '174', '175', '176', '177', '178', '20', '36', '38', '39', '58', '59', '60', '61', '62', '41', '37', '40', '16', '75', '33', '34', '35', '74', '102', '73', '96', '80', '81', '77', '142', '113', '110', '76', '116', '193', '196', '195', '194', '152', '184', '180', '183', '182', '181', '185', '153', '23', '150', '15', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '65', '111', '112', '125', '126', '127', '128', '129', '130', '131', '140', '141', '132', '95', '27', '82', '84', '85', '86', '88', '83', '87', '89', '90', '91', '92', '93', '19', '17', '26', '18', '67', '66', '71', '68', '69', '70', '186', '189', '190', '191', '192', '187', '188', '30', '55', '56', '114', '197', '154', '29')) AND (`e`.`entity_id` NOT IN('19', '21', '22', '23', '29', '70', '82', '89', '92', '93', '111', '112', '114', '120', '131', '154', '158', '159', '160', '161', '162', '164', '168', '169', '170', '171', '172', '173', '174', '175', '176', '179', '193', '194', '194', '195', '195', '196', '196')) 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');
Moja baza danych miała coś około 300mb, odchudziłem ją do około 40mb w poprzez poniższy sposób:
z tym, że TRUNCATE index_event; coś wywalało błąd.