Оптимизация/разбиение сложного sql-запроса

[holem]

Оптимизация/разбиение сложного sql-запроса

Здравствуйте, уважаемые!

Есть sql-запрос:
SELECT ... FROM `shop_items` , `shop_ext_attributes_values` AS av1, `shop_ext_attributes_values` AS av2
WHERE
`shop_items`.`node_id` = '1'
AND `shop_items`.`id` = av1.`item_id`
AND `shop_items`.`id` = av2.`item_id`
AND `av1`.`attr_id` = '23'
AND `av1`.`var_id` = '31'
AND `av2`.`attr_id` = '2'
AND `av2`.`var_id` = '152'
LIMIT 0 , 20

Запрос делает выборку позиций товара из основной таблицы shop_items:
id | node_id | ... (параметры позиции)
делая сортировку по таблице shop_ext_attributes_values, которая содержит дополнительные параметры позиций:
id | attr_id (id доп параметра) | var_id (выбранный вариант-значение атрибута, доступного для этого доп. параметра)

Так вот этот запрос делает сортировку не по одному доп. параметру, а по двум сразу (av1 и av2).
По одному параметру всё делается достаточно быстро, а по двум просто ужас.. ну оочень медленно.. примерно за 7-10 сек.. Очень нужно его оптимизировать. Если у кого есть какие идеи, прошу высказываеться.

Заранее большое спасибо за любую помощь.

 

[.des.]

explain, структура таблиц, размер таблиц?

И вообще странный запрос. Попробуйте так.

SELECT ...
FROM shop_items AS S
    INNER JOIN shop_ext_attributes_values AS A ON S.id=A.item_id
WHERE S.node_id=1 AND 
     A.attr_id IN (2,23) AND A.var_id IN (31,152)

-~{}~ 28.03.06 17:26:

Вчитался внимательнее .. это не равноценные запросы
Попробуйте привести експлайны и общие пояснения, что именно вы хотите получить из этих таблиц.
Все же запрос читается удобнее если параметры объединения отделены от фильтрации.

SELECT ...
FROM shop_items AS S
    INNER JOIN shop_ext_attributes_values AS A1 ON 
       S.id=A1.item_id AND
       A1.attr_id=23 AND
       A1.var_id=31
    INNER JOIN shop_ext_attributes_values AS A2 ON 
       S.id=A2.item_id AND
       A2.attr_id=2 AND
       A2.var_id=152 
WHERE S.node_id=1

 

[holem]

SELECT 
       S.`id` as item_id, 
       S.`node_id` as item_node_id, 
       S.`price` as item_price, 
       S.`name` as item_name, 
       S.`visible` as item_visible, 
       S.`new` as item_new, 
       S.`hit` as item_hit, 
       S.`soon` as item_soon, 
       S.`special` as item_special, 
       S.`sale` as item_sale, 
       S.`presence` as item_presence, 
       S.`regional` as item_regional, 
       S.`code` as item_code, 
       S.`filename` as item_filename, 
       S.`alt` as item_alt, 
       S.`full_filename` as item_full_filename, 
       S.`full_alt` as item_full_alt, 
       S.`time` as item_time
FROM shop_items AS S
    INNER JOIN shop_ext_attributes_values AS A1 ON 
       S.id=A1.item_id AND
       A1.attr_id=23 AND
       A1.var_id=31
    INNER JOIN shop_ext_attributes_values AS A2 ON 
       S.id=A2.item_id AND
       A2.attr_id=2 AND
       A2.var_id=152 
WHERE S.node_id=1
LIMIT 0,20

Сделал explain этого запроса, получил следующее:

id|select_type|table|type |possible_keys| key  |key_len |ref |rows  | Extra
1 | SIMPLE    | A1  | ALL |  NULL 	| NULL |NULL    |NULL|10652 | Using where
1 | SIMPLE    | A2  | ALL |  NULL 	| NULL |NULL    |NULL|10652 | Using where
1 | SIMPLE    | S   | ALL |  NULL 	| NULL |NULL    |NULL|707   | Using where

взял старый запрос (см. 1-й пост), получил то же самое.

Размеры таблиц :
1. shop_items (707 записей, 870 кб)
2. shop_ext_attributes_values (10652 записи, 900 кб)

.des., при использовании вашего запроса получилось немного ускорить процесс, секунды на 2-3, но всё же пока долговато.
Ещё есть один момент: полей для выборки достаточно много (около 20), попробывал убрать их, оставил только первые 2 поля - выборка заняла доли секунды. Может есть смысл как-то разбить запрос? хотя удобнее, конечно, одним запросом.

-~{}~ 28.03.06 19:13:

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

 

[.des.]

хм.. нет индексов? даже primary ключа нет? В чем создавали таблицы?
что показывает:
show create table shop_ext_attributes_values;
show create table shop_items;

Попробуйте добавить индексы на id, node_id и
составной на item_id, attr_id, var_id

-~{}~ 28.03.06 18:27:

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

Если нет индексов, то проблема не решена, запрос просто попал в кэш.

 

[holem]

Primary ключи на id есть у обоих таблиц. Индексов нет, только первичные id.
Интегрировал в систему - попробывал скорость - все отлично. В системе запрос генерится автоматически, поэтому возможный вариант с попаданием запроса в кеш отпадает.
Ещё раз спасибо .des. за помощь.

 

[zerkms]

попробуй на таблицу `shop_items` добавить индекс на поле `node_id`, а на таблицу `shop_ext_attributes_values` - составной индекс - на `id`, `attr_id`, `var_id`
потом так же покажи EXPLAIN