List:General Discussion« Previous MessageNext Message »
From:Nicholas Elliott Date:February 4 2003 6:57pm
Subject:Re: best way to optimize a table with many to few relationship.
View as plain text  
I'd recommend a column of type SET.

Read about it here: http://www.mysql.com/doc/en/SET.html

Basically, if your list of product types is static, this may be a good bet.  The SET type
can store any combination of 64 members,
stored as bits.
To list all items int a product type, you might use something similar to

SELECT * FROM products WHERE FIND_IN_SET('$mytype', type)>0;

I doubt that an index would be used at all -- but if you're reading 99% of the records
anyway, that's irrelevant.

Nick


----- Original Message -----
From: "Ken Easson" <ken@stripped>
To: <mysql@stripped>
Sent: Monday, February 03, 2003 10:07 PM
Subject: best way to optimize a table with many to few relationship.


> hello,
>
> I have a question about database structure and optimization.
>
> I have a shopping cart where several items can fall into several of many categories,
> but each item can also be several of a few
product types.
> my product type is limited to aprox. 5 product types. Further 99% of items will be in
> all 5 product types.
>
> I am trying to figure out the best way to optimize the relation ship between product
> type and product.
>
> if i create a product type table and then list each product in this type, i will have
> close to 5 times the number of rows as i
do number of products. since almost all products will be in all product type lists.
Conversely i can do an "exceptions table"
where a table lists the product types that a product is NOT in. This of course add's a
degree of difficulty if a new product type
is added.
>
> the other way of solving my problem is to add a "types" column to my product table,
> and adding the product type as a list,
however this seems strangely NOT relational database savy:
> SELECT * FROM products WHERE type IN ($mytype);
>
> OR since i have such a small number of product types, does it make sense to create a
> table which contains each product id, with
a column for each product type with a boolean field type? If this is recommended, how is
this table optimized?
>
> here is a sample of my data to illustrate where i'm coming from:
>
> item: clown
> categories: humorous, figure,
> product: pad, case, appliance
>
> item: robot-boy
> categories: si-fi, figure, abstract, robots, boys
> product: pad, case, appliance
>
> item: robot-girl
> categories: si-fi, figure, abstract, robots, girls
> product: pad, case, appliance
>
> item: dragonship
> categories: si-fi, animals, fantasy, space
> product: pad, case
>
> SELECT QUERY MYSQL
>
> ken easson
> justken.net
> ken@stripped
> justken web programming and technical support.
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
>
> To request this thread, e-mail <mysql-thread131571@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-nelliott=zedxinc.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Thread
best way to optimize a table with many to few relationship.Ken Easson4 Feb
  • Re: best way to optimize a table with many to few relationship.Nicholas Elliott4 Feb
RE: best way to optimize a table with many to few relationship.Peter Grigor4 Feb
Re: best way to optimize a table with many to few relationship.Alec.Cawley4 Feb