designing products features database
Moderator: General Moderators
designing products features database
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?
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?
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
Re: designing products features database
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
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
Shouldn't be a problem. Perhaps when you get to that query we can help you optimize it.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 :/
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Re: designing products features database
here is my query:
it tooks about 0.7 sec :/
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 ) =3Re: designing products features database
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
indexes existsonion2k wrote: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
Interesting. What do you get if you EXAMINE the sql?
Re: designing products features database
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
1 SIMPLE p eq_ref PRIMARY PRIMARY 4 f.prod_id 1
Re: designing products features database
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
yes i believe that is HAVING problem but how should i write query then?