Меню
Что нового?

Товарный каталог, структура БД, подбор по параметрам

  • Автор темы Автор темы Agent
  • Дата начала Дата начала

Agent

Новичок
Товарный каталог, структура БД, подбор по параметрам

Добрый день, пытаюсь создать товарный каталог, подумал над структорой, сделал такую:

# Обзор goods_items - общие данные о товаре (модель, брэнд, картинка и тд.)

# Обзор goods_param_names - названия параметров:
id - номер параметра
cat_id - номер категории (утюги, фены ...)
type - тип параметра, числовой или нет

# Обзор goods_param_values - значения не числовых параметров
id - номер значения параметра
par_id - номер параметра
value - само значение (допустим тип загрузки - верхний)

# Обзор goods_param_sync - таблица для связи параметров
good_id - номер товара
cat_id - номер категории
par_id - номер параметра
value - значение или номер значения параметра (для не числовых параметров)

Собственно все очень даже не плохо работает, пока не каснешься подбора по параметрам, пробовал 2 запроса

Код: 
SELECT good_id
FROM `goods_param_sync` AS p
 INNER JOIN `goods_param_sync` AS p1 ON p.good_id = p1.good_id
 INNER JOIN `goods_param_sync` AS p2 ON p.good_id = p2.good_id
....
 INNER JOIN `goods_param_sync` AS p16 ON p.good_id = p16.good_id
 INNER JOIN `goods_param_sync` AS p17 ON p.good_id = p17.good_id 
WHERE (p.`par_id` = 100 AND p.`value` =121)
 AND (p1.`par_id`=130 AND p1.`value` != 0)
 AND (p2.`par_id`=205 AND p2.`value` != 0)
 AND (p3.`par_id`=152 AND p3.`value` != 0)
 AND (p4.`par_id`=173 AND p4.`value` != 0)
 ....
 AND (p17.`par_id`=164 AND p17.`value` != 0);

при малом кол-ве параметров поиска, работает, при большом просто вешает мускул.

и второй
Код: 
SELECT good_id
FROM `goods_param_sync`
WHERE (`par_id` =100 AND `value` =121)
OR (`par_id`=130 AND `value` != 0)
OR (`par_id`=205 AND `value` != 0)
OR (`par_id`=152 AND `value` != 0)
....
OR (`par_id`=171 AND `value` != 0)
OR (`par_id`=169 AND `value` != 0)
OR (`par_id`=164 AND `value` != 0)
GROUP BY `good_id`
HAVING count( `good_id` ) = 17;

Работает, но очень медленно, на данный момент в таблице `goods_param_sync` порядка 500 тыс. параметров.

потому возник вопрос, как иначе можно организовать хранение данных, или как иначе при существующей структуре организовать подбор по параметрам? Может где в запросах лоханулся или в структуре в целом, буду очень благодарен за любую помощь.
 
tf, слабо представляю такой запрос, всяко будет столько же запросов, сколько параметров для поиска.
 
для того, чтоб быстро работал поиск, надо денормолизовать БД
а еще лучше использовать сфинкс или иную сеач машину (если конечно у тебя не говнохостинг).
если все-таки говнохостинг, то забудь о быстром извлечении информации из БД в принципе.
 
дык он не может быть нулл то о_О
если я правильно понимаю, то
Код: 
select good_id from good_param_sync where par_id in (1,2,3,5)
group by good_id
having count(par_id) = 4
выведет товары у которых установленны все искомые параметры.

непонятно что делает cat_id в good_param_sync
и зачем было выносить значения в отдельную таблицу.
 
Agent, SELECT WHERE id IN(SELECT param) по крайней мере если у тебя индексы не цепляются может помочь
 
Alexandre, можете пояснить что вы понимаете под "денормолизовать БД"

EXPLAIN с JOINами
Код: 
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	p11 	range 	par_id,good_id,par_id_2 	par_id_2 	7 	NULL 	9 	Using where
1 	SIMPLE 	p 	eq_ref 	par_id,good_id,par_id_2 	par_id 	10 	.p11.good_id,const,const 	1 	Using where
1 	SIMPLE 	p2 	ref 	par_id,good_id,par_id_2 	good_id 	3 	.p.good_id 	65 	Using where
1 	SIMPLE 	p3 	ref 	par_id,good_id,par_id_2 	good_id 	3 	.p.good_id 	65 	Using where
1 	SIMPLE 	p4 	ref 	par_id,good_id,par_id_2 	good_id 	3 	.p.good_id 	65 	Using where
1 	SIMPLE 	p5 	ref 	par_id,good_id,par_id_2 	good_id 	3 	.p.good_id 	65 	Using where

EXPLAIN с HAVING
Код: 
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	goods_param_sync 	index 	NULL 	par_id 	10 	NULL 	111415 	Using where; Using index

"(`par_id`=130 AND `value` != 0)" проверяем чтобы параметрт 130 не был равен 0, т.е. 1- есть, 0 - нет, если null то нет данных

"непонятно что делает cat_id в good_param_sync" написал запросы в общих словах, в оригинале, в запросе еще стоит cat_id, чтобы выборка шла допустим только по фенам.
 
tf, спасибо, я знаю, что это значит, вопрос стоял в том, как это сделать конкретном случае.
 
Автор оригинала: Agent
Код: 
SELECT good_id
FROM `goods_param_sync` AS p
 INNER JOIN `goods_param_sync` AS p1 ON p.good_id = p1.good_id
 INNER JOIN `goods_param_sync` AS p2 ON p.good_id = p2.good_id
....
 INNER JOIN `goods_param_sync` AS p16 ON p.good_id = p16.good_id
 INNER JOIN `goods_param_sync` AS p17 ON p.good_id = p17.good_id 
WHERE (p.`par_id` = 100 AND p.`value` =121)
 AND (p1.`par_id`=130 AND p1.`value` != 0)
 AND (p2.`par_id`=205 AND p2.`value` != 0)
 AND (p3.`par_id`=152 AND p3.`value` != 0)
 AND (p4.`par_id`=173 AND p4.`value` != 0)
 ....
 AND (p17.`par_id`=164 AND p17.`value` != 0);

при малом кол-ве параметров поиска, работает, при большом просто вешает мускул.
Нажмите для раскрытия...
Предполагаю, что JOIN'ов там хорошо за десяток должно быть? Оптимизатор скорее всего давится на полном переборе возможных планов выполнения. В Postgres'е, например, для таких случаев есть генетический оптимизатор.
 
Sad Spirit, на 17 JOIN уже валит, при 500 000 параметров в базе. Вариант с PostgreSQL к сожалению не доступен, надо как то извернуться с mysql...
 
Автор оригинала: Agent
Sad Spirit, на 17 JOIN уже валит, при 500 000 параметров в базе.
Нажмите для раскрытия...
А тут скорее всего кол-во параметров в базе ни на что и не влияет.

Вариант с PostgreSQL к сожалению не доступен, надо как то извернуться с mysql...
Нажмите для раскрытия...
Тогда вариант только: не писать 17 JOIN'ов. Т.е. как-нибудь сделать денормализацию, например хранить в таблице связи товара с атрибутами не один атрибут, а 10.
 
вопрос стоял в том, как это сделать конкретном случае
Нажмите для раскрытия...
вывести в одну или несколько плоских таблиц.
все зависит от того что тебе конкретно нужно, и подготавливаешь данные для быстрого извлечения, строишь вспомогательные тбл.

хочешь чтоб все летало, у тебя должно быть не более одного джоина.

лично я определил абстракцию "модель поиска"
для каждой модели подготовил некоторые таблицы и запросы.

Далее я анализирую запрашиваемые в поиске слова, по хешу (исп мемкеш) определяю к каким категориям они относятся, выбираю на основании категорий - модель поиска и подствавляю эти слова в модель.

запускаю SQL и формирую результаты.
 
Может имеет смысл вовсе создать одну таблицу с 150 полями (обычно больше параметров не бывает) и туда пихать параметры, единственное что работать с ней будет крайне неудобно...
 
На таблицу со 150 полями потребуется 150 индексов. Не уверен, что мыскль не обо@#ётся.

-~{}~ 06.10.09 13:33:

И, кстати, умеет ли мыскль использовать одновременно несколько индексов для поиска по одной таблице?
 
И, кстати, умеет ли мыскль использовать одновременно несколько индексов для поиска по одной таблице?
Нажмите для раскрытия...
умеет, но в ограниченном ряде случаев. гуглить по "Index Merge"
 
Войдите или зарегистрируйтесь для ответа.
Сверху