Здравствуйте, уважаемые коллеги!

Помогите, пожалуйста, вот по какому вопросу:

Есть некая таблица (MySql), к примеру:



Как наиболее эффективно вывести список тех product_id, где к примеру (attribute_id = 1 OR attribute_id = 4) AND (attribute_id = 17)

Пояснение: в данном примере по условию выше подходит product_id=2 и product_id=4, 
т.к. у product_id=2 есть attribute_id=1 и attribute_id=17 (т.е. условие, когда должны быть атрибуты ((1 и/или 4) И 17 выполнены).
Так же подходит и product_id=4, где выполнены условия (есть и (1 и/или 4) И есть атрибут с айди 17).

Один (очень медленный) вариант найти эти product_id так:

SELECT DISTINCT article_id FROM article_attributes
WHERE (article_id) IN
(SELECT article_id FROM article_attributes WHERE attribute_id = 1 OR attribute_id = 4)
AND article_id IN (SELECT article_id FROM article_attributes WHERE attribute_id = 17);

Но может быть есть более эффективный и быстрый способ? 

Это сообщение отредактировал(а) linuxoid - 27.10.2012, 23:59

Не знаю, по скорости у Акины может лучше будет, но как вариант...

Это сообщение отредактировал(а) FINANSIST - 29.10.2012, 08:39

При правильном индексе и сравнительно высокой селективности по нему - твой вариант быстрее. Но мой универсальнее.

При этих условиях и авторский вариант весьма не плох вроде как. 
Единственно, я бы попробовал один из двух in, с наибольшей селективностью заменить на кореллированный exists.


А так вобще... тема - прекрасный пример, показывающий истинную цену кажущейся простоты EAV, UDA

Это сообщение отредактировал(а) Zloxa - 30.10.2012, 10:57

Коллеги! Спасибо за предложения. К сожалению вариант Akina не работает, а FINANSIST (пробовал без индекса и с индексом на product_id + attribute_id) срабатывает примерно за 59 секунд, в то время как мой вариант отрабатывает макимум за 1 секунду (60000 продуктов, в таблице всего 630685 записей). 



Но это очень долго, т.к. к примеру есть 60 категорий, для которых надо посчитать кол-во товара с учетом входящих в фильтр атрибутов (к примеру "(красный ИЛИ зеленый) И летний", т.е. по аналогии с (1 OR 4) AND 17)

У мужика одного есть очень грамотная статья:

http://explainextended.com/2010/04/02/mult...-vs-not-exists/

Он там приводит пример скоростного запроса, но у него идет как бы рейндж для option1 (к примеру - цвет, от белого до пурпурного), рейндж для option2 (к примеру размер - от S до XXL), рейндж для option3 (к примеру сезон - от летнего до зимнего). Проблема в том, что я не знаю как можно для optionX указать несколько рейнджей (например ЦВЕТ от белого до красного ИЛИ от зеленого до пурпурного, при этом промежуточные цвета пропускаем/не учитываем, т.е. в данной концепции это можно считать как несколько рейнджей). Точнее я знаю, как это сделать для варианта GROUP BY, который он приводит в своей статье, но не понимаю, как сделать несколько рейнджей в его самом оптимальном запросе. Коллеги, может быть кому то будет интересно посмотреть статью, взгляните, пожалуйста. Я там в комментариях задал вопрос (phpcoder) на эту тему, но пока автор не ответил. В целом, в случае с GROUP BY, который в статье рассматривается, лично у меня запрос тоже работает 1 секунду, а это слишком долго :( Хотя в его примере запрос выполняется за 320 ms, но у него поменьше продуктов.
P.S. индекс я создал у себя такой 



Т.е., к примеру есть такая таблица



Тогда можем выбрать продукт, у которого (цвет зеленый (1, option=1) ИЛИ цвет красный (3, option=1)) И размер = XL (17, option=5) И сезон летний (22, option=6)



Данный запрос работает так же 1 секунду на 60000 продуктов (в таблице всего 630685 записей). Но у автора статьи есть более оптимальный вариант, но как прикрутить туда вариант типа (1 ИЛИ 3) И 17 И 22 я не знаю. На данный момент тот запрос сделан так, что можем выбрать ОТ 1 ДО 3 И ОТ 17 ДО 17 И ОТ 22 ДО 22, но не 1 ИЛИ 3 И ...

Это сообщение отредактировал(а) linuxoid - 29.10.2012, 21:58

А ваш "рабочий" запрос не от ваших исходных данных. article_id, используемый в нем не описан в описании, и не показан в исходном наборе данных



этот индекс вашему запросу ни о чем.

Создайте такой (attribute_id,product_id)

и попробуйте так:


Если вдруг окажется что запрос не использует индекса, попробуйте его зафорсить.

Совершенно не понятно нужен тут дистинкт или нет, как правило, при подобной организации пара  (attribute_id,product_id) уникальна и она таки выглядит таковой в вашем первом примере исходных данных. Однако во втором примере, все совсем по другому. Наверное опять пример не от тех данных не к тому запросу.

Это сообщение отредактировал(а) Zloxa - 30.10.2012, 00:46

Zloxa, виноват! Во втором примере просто добавился еще 1 колумн, но он теперь там называется "attribute_id", а бывший колумн теперь называется "attribute_value" что вводит в заблуждение. Это по аналогии EAV (entity, attribute, value), где во втором моем примере в роли attribute (он же option) выступает attribute_id (к примеру ЦВЕТ), а его значение, value (ЗЕЛЕНЫЙ, КРАСНЫЙ) - это attribute_value (бывший attribute_id из 1го примера). 

То что ты предложил меня впечатляет. Теперь запрос по тем же 60000 работает за в среднем 0.75 сек, что на данный момент является самым скоростным решением из тех, что я опробовал. И да - насчет первого примера (и во втором это тоже справедливо) - "Совершенно не понятно нужен тут дистинкт или нет, как правило, при подобной организации пара  (attribute_id,product_id) уникальна и она таки выглядит таковой в вашем первом примере исходных данных." - действительно не нужен, т.к. я изначально задумал, чтобы к примеру ЦВЕТ и РАЗМЕР не пересекались (чтобы можно было не использовать таблицу, как во втором примере).

Спасибо за квери!

Насчет скорости 0.75 сек - это предел?

И еще 1 нюанс - если будем использовать не 2 разных критерия (ЦВЕТ(1,4), РАЗМЕР(17)), а скажем 6, то и скорость квери значительно упадет? 

Типа:



В данном случае уже 0.87 сек. (добавили еще 1 фильтр)

Это сообщение отредактировал(а) linuxoid - 30.10.2012, 01:53

Запрос будет тем более производителен, чем более селективен предикат отбора по которому осуществляется индексный доступ и тем менее, чем этот предикат менее селективен.

нет, это назвается не фильтр, это называется semi-join
добавление фильтра на производительности отразилось бы не столь существенно.

да, ибо это EAV - worse design, через который таки надо пройти

Это сообщение отредактировал(а) Zloxa - 30.10.2012, 09:33

Zloxa, я тебя понял. Посоветуй, пожалуйста, какие есть ПРАВИЛЬНЫЕ на твой взгляд варианты решения проблемы по фильтру товаров? (в рамках MySQL, т.к. по идее можно воспользоваться NoSQL, где хороша реализация задачи + скорость - проверено, просто хотелось бы всё оставить в MySQL).

Это сообщение отредактировал(а) linuxoid - 30.10.2012, 10:40

Классикой. 
Провести анализ предметной области, систематизировать критерии отбора, наделить сущность товаров необходимым количеством аттрибутов, представить в виде таблицы, индексировать. Атрибуты, которые не задействованы в логике приложения, которые нужны только как доп информация на презентативном слое, вполне можно сохранить и в UDA(User Defined Attribute - вырожденный случай EAV, без Entity). Можно так же вынести в UDA атрибуты, по которым не предполагается фильтрация в совокупности с другими критериями по И, лишь по ИЛИ, но только лишь в случае, если этот атрибут используется только в фильтрации, никак не в логике, ибо тогда придется хардкодиться на код атрибута, что плохая практика.

Не гонитесь за универасльностью решения. Универсальность далеко не всегда нужна и имеет более высокую цену. Автомобиль-амфибия, хоть умеет и ездить и плавать, но и то и другое делает плохо. Хуже чем автомобиль, хуже чем катер. В велосипедостроении же гибкость не нужна. Велосипед должен быть надежен, прост в изготовлении, прост в обслуживании.

Это сообщение отредактировал(а) Zloxa - 30.10.2012, 11:48

Так то ж уже есть. Есть продукция, есть у неё атрибуты (цвет, размер, сезон и т.д.). Все это УЖЕ представлено в виде таблицы (т.е. product_id, option_name, option_value, к примеру одна из записей - продукт с айди 1 имеет (option_id=1)ЦВЕТ 3(option_value=3), имеет (option_id=1)ЦВЕТ 6(option_value=6)). Т.е. это таблица, в которой мы привязываем к артикулам значения различных опций (цвет, размер и т.д.). 

Сложность в том, что продуктов много, опций тоже много. Пользователю надо это как-то фильтровать. Типичный случай к примеру - пользователь выбрал цвет - КРАСНЫЙ, ЗЕЛЕНЫЙ, указал размер и категорию - к примеру платья. Тогда мы ему покажем, что в ПЛАТЬЯх есть кол-во X КРАСНЫХ или ЗЕЛЕНЫХ товаров с размером XL (к примеру, с учетом фильтра -> Платья (2637) <- count)

Уже много статей нашел, где товарищи рекомендуют подключить Sphinx, но это уже накладный солюшн.. В любом случае, Zloxa, спасибо за комментарии!

Все это уже представлено так, чтобы услжнить а не упростить отбор 

Чем обусловлен отказ от классического ER представления (product_id, name,color,size,weight) ?

Добавлено @ 12:26

Да, структуировать данные - основная работа.
Опустив ее, отдав на откуп структуре EAV, мол если вдруг чо - допилим, докрутим побыдному, мы не упрощаем, а усложняем себе жизнь.

Это сообщение отредактировал(а) Zloxa - 30.10.2012, 12:31

Zloxa, да, проще, чем уже просто видимо не получится. Буду исправляться. Спасибо.

Единственное, что смущает, если у продукта 25 цветов, 40 рамзеров, 2 сезона, 6 материалов и .т., то для 1 продукта получаем как минимимум 25 * 40 * 2 * 6 = 12000 записей.. не лучший вариант конечно. Т.е. в худшем (а может это и не худший) случае это, скажем 720 000 000 записей на 60 000 продуктов. Я правильно понимаю?

Это сообщение отредактировал(а) linuxoid - 30.10.2012, 13:45

Как максимум. А на самом деле гораздо меньше, даже не в разы - на порядки.