Search the Community

Привет, помогите пожалуйста решить проблему с медленными запросами, хостинг выдает такой медленный запрос: SELECT p.product_id, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.STATUS = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROM oc_product_discount pd2 WHEREpd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDERBY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM oc_product_special ps WHEREps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' ORps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '1' AND p.STATUS = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' GROUP BY p.product_id ORDER BY(p.quantity>0) DESC,p.date_added DESC, LCASE(pd.name) DESC LIMIT 0,10 Что это может значит и как можно убрать этот запрос?

Всем привет, сайт стал жутко тормозить, нашел медленный запрос Скан. рядов 4468 Получено рядов 1 Время 00:00:37 Текст запроса SELECT COUNT(DISTINCT p.product_id) AS total FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = \'1\' AND p.status = \'1\' AND p.date_available <= NOW() AND p2s.store_id = \'0\' AND (p2c.category_id = \'95\') Ищу исполнителя для оптимизации сего запроса за вознаграждение

SET timestamp=1540805733; # Query_time: 1.707224 Lock_time: 0.000562 Rows_sent: 25 Rows_examined: 808258 SELECT value_id, option_id, total FROM (SELECT p.manufacturer_id AS value_id, 'm' AS option_id, COUNT(p.manufacturer_id) AS total FROM oc_product p LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id) WHERE p.status = '1' AND p2c.category_id = '82' AND p.product_id IN(SELECT oov2p1.product_id FROM oc_ocfilter_option_value_to_product oov2p1 INNER JOIN oc_ocfilter_option_value_to_product oov2p2 ON (oov2p1.product_id = oov2p2.product_id) WHERE oov2p1.option_id = '30425' AND (oov2p1.value_id = '1742734348' OR oov2p1.value_id = '2076164755') AND oov2p2.option_id = '30454' AND (oov2p2.value_id = '1093219734')) GROUP BY p.manufacturer_id UNION ALL SELECT p.stock_status_id AS value_id, 's' AS option_id, COUNT(p.stock_status_id) AS total FROM oc_product p LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id) WHERE p.status = '1' AND p2c.category_id = '82' AND (p.manufacturer_id = '66') AND p.product_id IN(SELECT oov2p1.product_id FROM oc_ocfilter_option_value_to_product oov2p1 INNER JOIN oc_ocfilter_option_value_to_product oov2p2 ON (oov2p1.product_id = oov2p2.product_id) WHERE oov2p1.option_id = '30425' AND (oov2p1.value_id = '1742734348' OR oov2p1.value_id = '2076164755') AND oov2p2.option_id = '30454' AND (oov2p2.value_id = '1093219734')) GROUP BY p.stock_status_id UNION ALL SELECT oov2p.value_id, oov2p.option_id, COUNT(p.product_id) AS total FROM oc_ocfilter_option_value_to_product oov2p LEFT JOIN oc_product_to_category p2c ON (oov2p.product_id = p2c.product_id) LEFT JOIN oc_product p ON (oov2p.product_id = p.product_id) WHERE oov2p.value_id > '0' AND p.status = '1' AND p2c.category_id = '82' AND (p.manufacturer_id = '66') AND p.product_id IN(SELECT oov2p1.product_id FROM oc_ocfilter_option_value_to_product oov2p1 INNER JOIN oc_ocfilter_option_value_to_product oov2p2 ON (oov2p1.product_id = oov2p2.product_id) WHERE oov2p1.option_id = '30425' AND (oov2p1.value_id = '1742734348' OR oov2p1.value_id = '2076164755') AND oov2p2.option_id = '30454' AND (oov2p2.value_id = '1093219734')) GROUP BY oov2p.option_id, oov2p.value_id UNION ALL SELECT oov2p.value_id, oov2p.option_id, COUNT(p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id) LEFT JOIN oc_ocfilter_option_value_to_product oov2p ON (p.product_id = oov2p.product_id) WHERE oov2p.option_id = '30425' AND p.status = '1' AND p2c.category_id = '82' AND (p.manufacturer_id = '66') AND p.product_id IN(SELECT oov2p1.product_id FROM oc_ocfilter_option_value_to_product oov2p1 WHERE oov2p1.option_id = '30454' AND (oov2p1.value_id = '1093219734')) GROUP BY oov2p.option_id, oov2p.value_id UNION ALL SELECT oov2p.value_id, oov2p.option_id, COUNT(p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id) LEFT JOIN oc_ocfilter_option_value_to_product oov2p ON (p.product_id = oov2p.product_id) WHERE oov2p.option_id = '30454' AND p.status = '1' AND p2c.category_id = '82' AND (p.manufacturer_id = '66') AND p.product_id IN(SELECT oov2p1.product_id FROM oc_ocfilter_option_value_to_product oov2p1 WHERE oov2p1.option_id = '30425' AND (oov2p1.value_id = '1742734348' OR oov2p1.value_id = '2076164755')) GROUP BY oov2p.option_id, oov2p.value_id) results LIMIT 0, 25;

Здравствуйте. Помогите пожалуйста, кто может, Долго грузятся прайсы с товарами, больше 12 000 шт., при загрузке большого кол-ва выкидывает с админки с ошибкой 504 Gateway Time-out. Обнаружила медленные запросы, больше недели с одного IP ежедневно по 6-8 шт., время 6-9 секунд. Один из: SELECT (IF (LCASE(pd.name) LIKE '%goodyear ultra grip ice 2%', 60, 0) + IF (LCASE(pd.name) LIKE'goodyear%', 80, 0) + IF (LCASE(pd.name) LIKE '%goodyear%', 8, 0) + IF (LCASE(pd.name) LIKE 'ultra%', 80, 0) + IF (LCASE(pd.name) LIKE '%ultra%', 8, 0) + IF (LCASE(pd.name) LIKE 'grip%', 80, 0) + IF (LCASE(pd.name) LIKE'%grip%', 8, 0) + IF (LCASE(pd.name) LIKE 'ice%', 80, 0) + IF (LCASE(pd.name) LIKE '%ice%', 8, 0) + IF(LCASE(pd.name) LIKE '2%', 80, 0) + IF (LCASE(pd.name) LIKE '%2%', 8, 0) + IF (LCASE(pd.description) LIKE'%goodyear ultra grip ice 2%', 20, 0) + IF (LCASE(pd.description) LIKE '%goodyear%', 2, 0) + IF(LCASE(pd.description) LIKE '%ultra%', 2, 0) + IF (LCASE(pd.description) LIKE '%grip%', 2, 0) + IF(LCASE(pd.description) LIKE '%ice%', 2, 0) + IF (LCASE(pd.description) LIKE '%2%', 2, 0) + IF (LCASE(pd.tag)LIKE '%goodyear%', 9, 0) + IF (LCASE(pd.tag) LIKE '%ultra%', 9, 0) + IF (LCASE(pd.tag) LIKE '%grip%', 9, 0) + IF(LCASE(pd.tag) LIKE '%ice%', 9, 0) + IF (LCASE(pd.tag) LIKE '%2%', 9, 0)) AS relevance, p.product_id FROMoc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOINoc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_attribute pa ON(p.product_id = pa.product_id) WHERE pd.language_id = '1' AND p.STATUS = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND ( (LCASE(pd.name) LIKE '%goodyear%') OR (LCASE(pd.name) LIKE'%ultra%') OR (LCASE(pd.name) LIKE '%grip%') OR (LCASE(pd.name) LIKE '%ice%') OR (LCASE(pd.name) LIKE'%2%') OR (LCASE(pd.description) LIKE '%goodyear%') OR (LCASE(pd.description) LIKE '%ultra%') OR(LCASE(pd.description) LIKE '%grip%') OR (LCASE(pd.description) LIKE '%ice%') OR (LCASE(pd.description)LIKE '%2%') OR (LCASE(pd.tag) LIKE '%goodyear%') OR (LCASE(pd.tag) LIKE '%ultra%') OR (LCASE(pd.tag) LIKE'%grip%') OR (LCASE(pd.tag) LIKE '%ice%') OR (LCASE(pd.tag) LIKE '%2%') OR (LCASE(pa.text) LIKE'%goodyear%' AND pa.language_id = '1' ) OR (LCASE(pa.text) LIKE '%ultra%' AND pa.language_id = '1' ) OR(LCASE(pa.text) LIKE '%grip%' AND pa.language_id = '1' ) OR (LCASE(pa.text) LIKE '%ice%' AND pa.language_id = '1' ) OR (LCASE(pa.text) LIKE '%2%' AND pa.language_id = '1' ) OR (LCASE(p.model) LIKE '%goodyear%') OR(LCASE(p.model) LIKE '%ultra%') OR (LCASE(p.model) LIKE '%grip%') OR (LCASE(p.model) LIKE '%ice%') OR(LCASE(p.model) LIKE '%2%') OR (LCASE(p.sku) = 'goodyear') OR (LCASE(p.sku) = 'ultra') OR (LCASE(p.sku) = 'grip') OR (LCASE(p.sku) = 'ice') OR (LCASE(p.sku) = '2') OR (LCASE(p.upc) = 'goodyear') OR (LCASE(p.upc) = 'ultra') OR (LCASE(p.upc) = 'grip') OR (LCASE(p.upc) = 'ice') OR (LCASE(p.upc) = '2') OR (LCASE(p.ean) = 'goodyear') OR (LCASE(p.ean) = 'ultra') OR (LCASE(p.ean) = 'grip') OR (LCASE(p.ean) = 'ice') OR (LCASE(p.ean)= '2') OR (LCASE(p.jan) = 'goodyear') OR (LCASE(p.jan) = 'ultra') OR (LCASE(p.jan) = 'grip') OR (LCASE(p.jan) = 'ice') OR (LCASE(p.jan) = '2') OR (LCASE(p.isbn) = 'goodyear') OR (LCASE(p.isbn) = 'ultra') OR (LCASE(p.isbn) = 'grip') OR (LCASE(p.isbn) = 'ice') OR (LCASE(p.isbn) = '2') OR (LCASE(p.mpn) = 'goodyear') OR (LCASE(p.mpn) = 'ultra') OR (LCASE(p.mpn) = 'grip') OR (LCASE(p.mpn) = 'ice') OR (LCASE(p.mpn) = '2') ) GROUP BY p.product_id ORDER BY relevance DESC, LCASE(pd.name) ASC LIMIT 0,30 id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE p NULL ALL PRIMARY NULL NULL NULL 10089 3.33 Using where; Using temporary; Using filesort 1 SIMPLE p2s NULL eq_ref PRIMARY,store_id PRIMARY 8 diacar_db.p.product_id,const 1 100.00 Using index 1 SIMPLE pd NULL eq_ref PRIMARY,language_id PRIMARY 8 diacar_db.p.product_id,const 1 100.00 NULL 1 SIMPLE pa NULL ref PRIMARY PRIMARY 4 diacar_db.p.product_id 749 100.00 Using where Вроде как, у некоторых таблиц не используются индексы и грубо сканирование идет. ПОМОГИТЕ установить правильно индексы или другие возможные варианты. Что проделано: Заказано на хостинке Memcache - не помогло. Отключено Подсчет кол-ва товаров; Отключено filterpro - не помогло.

Имеется магазин на ocStore, товаров много - и вот появилась проблема в виде 10 медленных запросов. Нужен специалист, который решит проблему. Интересует цена решения такого рода задачки.

Всем привет. Уважаемые форумчане помогите разобраться. Вот такие медленые запросы как от них избавиться. Спасибо. Запрос 1. SELECT a.attribute_group_id AS group_id, av.attribute_id AS attr_id, av.attribute_value_id AS val_id, agd.name AS group_name, ad.name AS attr_name, av.value FROM `oc_bf_aggregate_filters` AS af INNERJOIN ( SELECT p.* FROM `oc_product` AS p INNER JOIN `oc_product_to_category` AS p2c ON(p.product_id = p2c.product_id) INNER JOIN `oc_category_path` AS cp ON (cp.category_id = p2c.category_id) WHERE cp.path_id = '11') AS p ON (af.product_id = p.product_id) INNER JOIN`oc_bf_attribute_value` AS av ON (af.value = av.attribute_value_id) INNER JOIN `oc_attribute` AS a ON(a.attribute_id = av.attribute_id) INNER JOIN `oc_attribute_description` AS ad ON (ad.attribute_id = a.attribute_id) INNER JOIN `oc_attribute_group` AS ag ON (ag.attribute_group_id = a.attribute_group_id)INNER JOIN `oc_attribute_group_description` AS agd ON (agd.attribute_group_id = a.attribute_group_id)INNER JOIN `oc_product_to_store` AS ps ON (p.product_id = ps.product_id) WHERE agd.language_id ='2' AND ad.language_id = '2' AND av.language_id = '2' AND ps.store_id = '0' AND af.type = 'ATTRIBUTE'GROUP BY av.attribute_value_id ORDER BY ag.sort_order, ag.attribute_group_id, a.sort_order, ad.name, av.sort_order, av.value Запрос 2. SELECT p.product_id, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.STATUS = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROMoc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' ANDpd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) ASdiscount, (SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id ANDps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND(ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT1) AS special FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) WHEREpd.language_id = '2' AND p.STATUS = '1' AND p.date_available <= NOW() AND p2s.store_id = '0'GROUP BY p.product_id ORDER BY p.date_added DESC, LCASE(pd.name) DESC LIMIT 0,4 Запрос 3. INSERT IGNORE INTO `oc_bf_aggregate_filters` (type,group_id,product_id,value) SELECT 'ATTRIBUTE', av.attribute_id, product_id, attribute_value_id FROM `oc_bf_tmp` AS pa LEFT JOIN`oc_bf_attribute_value` AS av ON (pa.value = av.value AND av.language_id = pa.language_id ANDav.attribute_id = pa.attribute_id) WHERE av.value IS NOT NULL

Уважаемые гуру SQL Нужно сделать оптимизацию сайта. На сайт залил чуть более 3000 товара и он грузится не по детски долго ... ХЕЛП Присутствующие медленные запросы. SELECT DISTINCT p.product_id, ( SELECT cd.name FROM `oc_product_to_category` p2c LEFT JOIN`oc_category_description` cd ON (p2c.category_id = cd.category_id) WHERE p2c.main_category = 1 ANDp2c.product_id = p.product_id LIMIT 1) AS main_category ,REPLACE (pd.name, '"', '\"') ASname,REPLACE (p.model, '"', '\"') AS model,p.sku,p.upc,m.name ASmanufacturer,p.shipping,p.location,TRUNCATE(p.price, 2) ASprice,p.points,p.quantity,p.stock_status_id,p.LENGTH,p.width,p.height,p.weight,(SELECT su.keyword FROM`oc_url_alias` su WHERE su.query = CONCAT('product_id=', p.product_id) LIMIT 1) ASkeyword,pd.seo_title,pd.seo_h1,pd.meta_keyword,pd.meta_description,pd.description,p.image,p.sort_order,p.STATUSFROM oc_product p LEFT JOIN `oc_product_description` pd ON (p.product_id = pd.product_id) LEFT JOIN`oc_manufacturer` m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN `oc_product_to_category`p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = '1' AND (p.manufacturer_id IN(47,62,177,8,185,216,181,186,110,204,61,135,138,111,139,51,82,63,42,16,137,140,84,116,9,118,168,17,31,80,85,86,21,169,87,163,206,141,170,12,142,81,127,30,88,89,191,52,90,145,11,125,64,55,143,202,53,65,205,144,91,159,66,199,44,128,37,193,158,67,7,14,92,171,5,24,29,200,207,165,129,83,119,93,208,36,54,15,38,58,198,25,75,23,18,146,94,68,35,178,172,166,45,173,46,20,43,69,182,114,121,147,148,122,34,19,213,95,48,214,33,211,131,133,201,78,215,40,70,41,209,71,96,72,22,123,39,6,49,13,164,32,149,115,174,167,203,194,98,74,136,150,151,97,134,152,28,196,212,99,120,59,153,73,195,100,101,102,10,210,50,103,56,175,60,126,76,217,130,104,105,197,112,26,154,190,117,124,162,106,77,179,27,155,156,57,79,107,157,108,132,183,113,176,192,184,161,160,189,188,109,187))AND (p2c.category_id IN(253,254,255,256,259,258,257,249,251,250,181,189,184,188,183,202,185,186,182,187,121,84,101,103,237,236,118,238,122,148,147,144,125,124,123,119,239,93,224,88,109,100,89,77,220,78,214,132,102,79,143,240,112,73,221,74,81,213,133,75,219,76,87,246,94,96,141,113,136,114,139,142,137,138,135,243,245,244,241,242,228,68,80,252,85,145,248,159,247,83,169,67,150,151,203,210,212,204,206,205,190,218,223,191,192,193,194,199,196,198,197,195,154,229,157,234,235,231,233,230,158))ORDER BY p.product_id LIMIT 0, 10000