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

[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]

а вложенные запросы как себя ведут?

 

[Agent]

tf, слабо представляю такой запрос, всяко будет столько же запросов, сколько параметров для поиска.

 

[Alexandre]

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

 

[findnext]

а как насчёт индексов?

покажи explain запросов

 

[dr-sm]

что вот это условие означает "(`par_id`=130 AND `value` != 0)" ?

 

[findnext]

IS NOT NULL наверное

 

[dr-sm]

дык он не может быть нулл то о_О
если я правильно понимаю, то

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
и зачем было выносить значения в отдельную таблицу.

 

[tf]

Agent, SELECT WHERE id IN(SELECT param) по крайней мере если у тебя индексы не цепляются может помочь

 

[Agent]

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]

"денормолизовать БД"

http://tinyurl.com/ycknt8j

 

[Agent]

tf, спасибо, я знаю, что это значит, вопрос стоял в том, как это сделать конкретном случае.

 

[Sad Spirit]

Автор оригинала: 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'е, например, для таких случаев есть генетический оптимизатор.

 

[Agent]

Sad Spirit, на 17 JOIN уже валит, при 500 000 параметров в базе. Вариант с PostgreSQL к сожалению не доступен, надо как то извернуться с mysql...

 

[Sad Spirit]

Автор оригинала: Agent
Sad Spirit, на 17 JOIN уже валит, при 500 000 параметров в базе.

А тут скорее всего кол-во параметров в базе ни на что и не влияет.

Вариант с PostgreSQL к сожалению не доступен, надо как то извернуться с mysql...

Тогда вариант только: не писать 17 JOIN'ов. Т.е. как-нибудь сделать денормализацию, например хранить в таблице связи товара с атрибутами не один атрибут, а 10.

 

[Alexandre]

вопрос стоял в том, как это сделать конкретном случае

вывести в одну или несколько плоских таблиц.
все зависит от того что тебе конкретно нужно, и подготавливаешь данные для быстрого извлечения, строишь вспомогательные тбл.

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

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

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

запускаю SQL и формирую результаты.

 

[Agent]

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

 

[Sad Spirit]

На таблицу со 150 полями потребуется 150 индексов. Не уверен, что мыскль не обо@#ётся.

-~{}~ 06.10.09 13:33:

И, кстати, умеет ли мыскль использовать одновременно несколько индексов для поиска по одной таблице?

 

[zerkms]

И, кстати, умеет ли мыскль использовать одновременно несколько индексов для поиска по одной таблице?

умеет, но в ограниченном ряде случаев. гуглить по "Index Merge"