Page 1 of 1

designing products features database

Posted: Thu Jul 10, 2008 6:55 am
by kreoton
hi,

i have a problem designing database for new portal. So far we have 2 tables where we would store products categories and products. But problem is with products features tables, all product categories would have different number of features.

in example:
car would have these features: ABS, ESP, leather salon etc...
pc would have these: wireless, wifi, usb, dvd rw. etc

maybe someone have good solution how to design database and how to perform search in different categories by features?

Re: designing products features database

Posted: Thu Jul 10, 2008 11:09 am
by Ollie Saunders
You'll need two more tables. A features table and a features_products table that implements a many-to-many relationship between features and products. http://www.tekstenuitleg.net/en/article ... tutorial/8

Re: designing products features database

Posted: Fri Jul 11, 2008 1:46 am
by kreoton
we know about this solution but problem is that query that we build is a little bit slow, while performing search by features in database there is 300.000 products and more than 10.000 of searches in one hour :/

Re: designing products features database

Posted: Fri Jul 11, 2008 2:06 am
by s.dot
kreoton wrote:we know about this solution but problem is that query that we build is a little bit slow, while performing search by features in database there is 300.000 products and more than 10.000 of searches in one hour :/
Shouldn't be a problem. Perhaps when you get to that query we can help you optimize it. :)

Re: designing products features database

Posted: Fri Jul 11, 2008 5:25 am
by kreoton
here is my query:

Code: Select all

SELECT c.id, c.mark_id, c.price
FROM test_ann_products AS p
JOIN test_products_features AS f ON p.id = f.prod_id
WHERE f.feature_id
IN ( 1, 2, 3 )
GROUP BY f.prod_id
HAVING count( p.id ) =3
it tooks about 0.7 sec :/

Re: designing products features database

Posted: Fri Jul 11, 2008 5:48 am
by onion2k
Add an index on prod_id and feature_id in the test_products_features table ... it'll be a lot faster then.

Re: designing products features database

Posted: Fri Jul 11, 2008 6:11 am
by kreoton
onion2k wrote:Add an index on prod_id and feature_id in the test_products_features table ... it'll be a lot faster then.
indexes exists ;)

Re: designing products features database

Posted: Fri Jul 11, 2008 6:19 am
by onion2k
Interesting. What do you get if you EXAMINE the sql?

Re: designing products features database

Posted: Fri Jul 11, 2008 6:47 am
by kreoton
1 SIMPLE f range prod_id,feature_id feature_id 4 NULL 43924 Using where; Using temporary; Using filesort
1 SIMPLE p eq_ref PRIMARY PRIMARY 4 f.prod_id 1

Re: designing products features database

Posted: Fri Jul 11, 2008 6:51 am
by onion2k
It'll be the "Using temporary;" and "Using filesort" that's killing it. I imagine those are down to the HAVING statement and the lack of an ORDER BY.

Re: designing products features database

Posted: Fri Jul 11, 2008 6:55 am
by kreoton
yes i believe that is HAVING problem but how should i write query then?