designing products features database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kreoton
Forum Commoner
Posts: 42
Joined: Fri Mar 03, 2006 7:27 pm

designing products features database

Post 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?
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: designing products features database

Post 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
User avatar
kreoton
Forum Commoner
Posts: 42
Joined: Fri Mar 03, 2006 7:27 pm

Re: designing products features database

Post 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 :/
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: designing products features database

Post 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. :)
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.
User avatar
kreoton
Forum Commoner
Posts: 42
Joined: Fri Mar 03, 2006 7:27 pm

Re: designing products features database

Post 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 :/
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: designing products features database

Post by onion2k »

Add an index on prod_id and feature_id in the test_products_features table ... it'll be a lot faster then.
User avatar
kreoton
Forum Commoner
Posts: 42
Joined: Fri Mar 03, 2006 7:27 pm

Re: designing products features database

Post 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 ;)
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: designing products features database

Post by onion2k »

Interesting. What do you get if you EXAMINE the sql?
User avatar
kreoton
Forum Commoner
Posts: 42
Joined: Fri Mar 03, 2006 7:27 pm

Re: designing products features database

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: designing products features database

Post 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.
User avatar
kreoton
Forum Commoner
Posts: 42
Joined: Fri Mar 03, 2006 7:27 pm

Re: designing products features database

Post by kreoton »

yes i believe that is HAVING problem but how should i write query then?
Post Reply