Как можно ускорить sql запрос

[Jey Val Star]

Здравствуйте, есть бд состоящая из 3-х таблиц товары | связь товаров-параметров | параметры

Для вывода товаров по 4 параметрам есть запрос sql который выполняется в течении 3,5 сек

SELECT                                              off.id as id,     off.dopublic as dopublic,    off.automatic as automatic,    off.url as url,    off.name as name,    off.offer_type_id as offer_type_id,    off.original_id as original_id,     off.meta_title as meta_title,    off.meta_keywords as meta_keywords,    off.meta_description as meta_description     , MAX(IF(mop.param_id = 1, mpv.value_str, NULL)) AS idref      , MAX(IF(mop.param_id = 16, mpv.value_file, NULL)) AS part_info_serial    , MAX(IF(mop.param_id = 17, mpv.value_str, NULL)) AS manufacturer      , GROUP_CONCAT(IF(mop.param_id = 21, mpv.value_str, NULL) ORDER BY IF(mop.param_id = 21, mpv.value_str, NULL) SEPARATOR ', ') as group_name      , MAX(IF(mop.param_id = 25, mpv.value_str, NULL)) AS price_range      , MAX(IF(mop.param_id = 26, mpv.value_file, NULL)) AS combinations      , MAX(IF(mop.param_id = 18, mpv.value_file, NULL)) AS warehouse_info_serial      , MAX(IF(mop.param_id = 23, mpv.value_str, NULL)) AS description_full      , MAX(IF(mop.param_id = 22, mpv.value_int, NULL)) AS total_qty      , MAX(IF(mop.param_id = 24, mpv.value_str, NULL)) AS description_short      , MAX(IF(mop.param_id = 27, mpv.value_str, NULL)) AS kit      , MAX(IF(mop.param_id = 30, mpv.value_datetime, NULL)) AS photo_date    , MAX(IF(mop.param_id = 28, mpv.value_double, NULL)) AS min_old_price    , MAX(IF(mop.param_id = 3, mpv.value_double, NULL)) AS price    , GROUP_CONCAT(IF(mop.param_id = 4, mpv.value_str, NULL) ORDER BY IF(mop.param_id = 4, mpv.value_str, NULL) SEPARATOR ', ') as photo      , MAX(IF(mop.param_id = 29, mpv.value_str, NULL)) AS articul      , GROUP_CONCAT(IF(mop.param_id = 6, mpv.value_str, NULL) ORDER BY IF(mop.param_id = 6, mpv.value_str, NULL) SEPARATOR ', ') as material_name      , GROUP_CONCAT(IF(mop.param_id = 7, mpv.value_str, NULL) ORDER BY IF(mop.param_id = 7, mpv.value_str, NULL) SEPARATOR ', ') as material_color      , GROUP_CONCAT(IF(mop.param_id = 8, mpv.value_str, NULL) ORDER BY IF(mop.param_id = 8, mpv.value_str, NULL) SEPARATOR ', ') as probe_name      , GROUP_CONCAT(IF(mop.param_id = 9, mpv.value_str, NULL) ORDER BY IF(mop.param_id = 9, mpv.value_str, NULL) SEPARATOR ', ') as weight_name      , GROUP_CONCAT(IF(mop.param_id = 10, mpv.value_str, NULL) ORDER BY IF(mop.param_id = 10, mpv.value_str, NULL) SEPARATOR ', ') as size_name    , GROUP_CONCAT(IF(mop.param_id = 11, mpv.value_str, NULL) ORDER BY IF(mop.param_id = 11, mpv.value_str, NULL) SEPARATOR ', ') as count_stones      , GROUP_CONCAT(IF(mop.param_id = 12, mpv.value_str, NULL) ORDER BY IF(mop.param_id = 12, mpv.value_str, NULL) SEPARATOR ', ') as stone_name      , GROUP_CONCAT(IF(mop.param_id = 13, mpv.value_str, NULL) ORDER BY IF(mop.param_id = 13, mpv.value_str, NULL) SEPARATOR ', ') as stone_color      , GROUP_CONCAT(IF(mop.param_id = 14, mpv.value_str, NULL) ORDER BY IF(mop.param_id = 14, mpv.value_str, NULL) SEPARATOR ', ') as total_karat      , GROUP_CONCAT(IF(mop.param_id = 15, mpv.value_str, NULL) ORDER BY IF(mop.param_id = 15, mpv.value_str, NULL) SEPARATOR ', ') as stone_clean      , GROUP_CONCAT(IF(mop.param_id = 19, mpv.value_str, NULL) ORDER BY IF(mop.param_id = 19, mpv.value_str, NULL) SEPARATOR ', ') as stone_cut      , GROUP_CONCAT(IF(mop.param_id = 20, mpv.value_str, NULL) ORDER BY IF(mop.param_id = 20, mpv.value_str, NULL) SEPARATOR ', ') as stone_type FROM    mag_off as off               LEFT JOIN    mag_off_param as mop ON (mop.offer_id=off.id)LEFT JOIN    mag_param_val as mpv ON (mpv.id=mop.param_value_id)WHERE(off.dopublic = 1)GROUP BY off.idHAVING    (        (GROUP_CONCAT(CONCAT('A', IF(mop.param_id = 21, mpv.id, NULL),'B') SEPARATOR ',') LIKE ('%A210000033B%'))        OR        (GROUP_CONCAT(CONCAT('A', IF(mop.param_id = 21, mpv.id, NULL),'B') SEPARATOR ',') LIKE ('%A210000055B%'))        OR        (GROUP_CONCAT(CONCAT('A', IF(mop.param_id = 21, mpv.id, NULL),'B') SEPARATOR ',') LIKE ('%A210000081B%'))        OR        (GROUP_CONCAT(CONCAT('A', IF(mop.param_id = 21, mpv.id, NULL),'B') SEPARATOR ',') LIKE ('%A210000108B%'))    )    AND    (        (GROUP_CONCAT(CONCAT('A', IF(mop.param_id = 25, mpv.id, NULL),'B') SEPARATOR ',') LIKE ('%A250000003B%'))    )    AND    (        (GROUP_CONCAT(CONCAT('A', IF(mop.param_id = 6, mpv.id, NULL),'B') SEPARATOR ',') LIKE ('%A600000001B%'))    )    AND    (         (GROUP_CONCAT(CONCAT('A', IF(mop.param_id = 12, mpv.id, NULL),'B') SEPARATOR ',') LIKE ('%A120000004B%'))        OR        (GROUP_CONCAT(CONCAT('A', IF(mop.param_id = 12, mpv.id, NULL),'B') SEPARATOR ',') LIKE ('%A120000021B%'))        OR        (GROUP_CONCAT(CONCAT('A', IF(mop.param_id = 12, mpv.id, NULL),'B') SEPARATOR ',') LIKE ('%A120000025B%'))        OR        (GROUP_CONCAT(CONCAT('A', IF(mop.param_id = 12, mpv.id, NULL),'B') SEPARATOR ',') LIKE ('%A120000077B%'))    )    ORDER BY (MAX(IF(mop.param_id = 3, mpv.value_double, NULL))=0), price DESCLIMIT 0,20

Как его можно изменить, сохранив возможности и при этом ускорить до 0,35 сек?

PS товаров 1500, параметров более 35 000, записей в связке 44 000. Архитектуру БД изменять нельзя...

Edited February 20, 2015 by Jey Val Star

[Andryshok]

разбирать не видя структуры бд не особо правильно, попробуйте избавиться от LIKE и особенно важно от - OR, используйте union для объединения выборок

 

параметров более 35 000

не верю, если имеется в виду характеристики товара - то подозреваю что архитектору бд нужно забить в голову гвоздь, или тому кто вгружал товар. Подозреваю что одинаковый товар не имеет общих характеристик а на каждый завели новые.

Edited February 20, 2015 by Andryshok

[Jey Val Star]

Нашел решение

Тему можно закрыть