Здравствуйте. Помогите пожалуйста, кто может, Долго грузятся прайсы с товарами, больше 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 - не помогло.
Сама разобралась. Вдруг кому-то пригодится: в атрибутах не было указано "Урл для фильтра".
