Response intermixed:
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote on 10/21/2005 09:19:41 AM:
> Thanks Shawn.
> See comments:
>
> [EMAIL PROTECTED] wrote:
>
> >Sorry I didn't get back to you earlier today. I have had a busy day.
> >Comments embedded...
> >"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote on 10/20/2005 04:35:30 PM:
> >
<snip>
> >>CREATE TABLE products (
> >> prod_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
> >> prod_no VARCHAR(12) NOT NULL,
> >> prod_name VARCHAR(45) NOT NULL,
> >> prod_description TEXT NULL,
> >> prod_colors TEXT NULL, // since there are tons of colors, defined
> >>differently by different suppliers, I had to go this way for color
> >>
> >>
> >options
> >
> >
> >> prod_includes TEXT NULL,
> >> prod_catalog VARCHAR(45) NULL,
> >> prod_status ENUM('0','1') NOT NULL DEFAULT 0,
> >> prod_supplier VARCHAR(45) NULL,
> >> prod_start_date DATE NULL,
> >> prod_end_date DATE NULL,
> >> PRIMARY KEY(prod_id),
> >> INDEX products_index1(prod_status),
> >> INDEX products_index2(prod_start_date, prod_end_date)
> >>)
> >>TYPE=InnoDB;
> >>
> >>
> >
> >Good again.
> >if you don't want your application to parse out a list of colors from
the
> >colors field, you will need a table of just colors and another
association
> >table between colors and products.
> >
> >
> This is why I made color columns this way.
> Product 07V0128: Neutrals - White(30n) Heathers - Ash (93h), Sport
> Grey (95h) Colors - Gold (24c), Navy (32c), Forest Green (33c), Black
> (36c), Orange (37c), Red (40c), Royal (51c), Lt Blue (69c), Purple
> (81c), Maroon (83c)
> Product 07V0205: Neutrals: **White, Natural. Lights: *Ash, Graphite,
> **Light Steel. Darks - **Black, Bluestone, Cardinal, Copper, Daffodil
> Yellow, Denim Blue, Gold, Gold Nugget, Kelly Green, Light Blue, Lime,
> Moss, *Navy, Orange, Pink, Pebble, Pine, Purple, *Red
> Product 07V0560: Apricot, Burnt Orange, Butter, Cedar, Celery, Chalky
> Blue, Chalky Mint, Chalky Purple, Charcoal, Cigar, Columbia Blue,
> Crimson, Dandelion, Denim, Dijon, Dorm Green, Forest, Grasshopper, Hot
> Red, Indigo, Kelly, Key Lime, Latte, Lime, Mustard, Nantucke
> Since, there is NO RULE for colors, this is a only solution, right?
There are rules for colors. Your suppliers give you a list of all of the
combinations of colors for each product, right? That's a rule: "Product Y
comes in Midnight Blue, Aqua, and Cobalt". Any other color would be wrong,
correct?
Whenever you start making "lists" in text fields, you have the opportunity
to normalize those lists into their own tables. Sure, it adds a layer of
design but it's a more flexible design as you can create a product-color
association table that contains start dates and end dates for special
color runs or that contains a pointer to your inventory tables so that
when a particular color runs out of stock, you can stop listing it on your
site. As a rough example:
CREATE TABLE color_product {
color_id,
product_id,
inventory_sku,
startdate,
enddate,
);
Every color value (including all unique color combinations) for any
product are listed once (in their own table), product are listed once (in
their own table) and there is an entry in color_product for each
combination of product to color. The startdate and enddate columns could
be used for automatically expiring listings like black/orange around
Halloween or red/green/white around Christmas. That way when the holiday
season is over, you can stop listing it automatically based simply on the
current date (no manual deletions) .
> >Remember that the optimizer won't use an index if it thinks that the
index
> >will return over 30% or so of the records in the table. With only two
> >values in it, an index on prod_status (all by itself) will probably
never
> >have enough cardinality to be useful. Consider using it as part of a
> >compound index instead.
> >
> >
> Looks like I'm doing wrong for a loooong time :)
> Ok, then. Do I have to Index prod_status or it's not necessary?
It's useful information and because you probably use it in EVERY query,
adding it as a secondary column to each of your other indexes is probably
a good idea.
> Could you please give me more info on "Consider using it as part of a
> compound index instead"?
>
A compound index is simply an index that uses more than one column. If you
find that you are always making queries like
SELECT ....
FROM ...
WHERE category_id = xxx AND status=1
Then creating a compound index on (category_id, status) makes that query
(any any other query that uses those combinations of fields) more
responsive because the index is more selective (better cardinality). As an
added bonus it would also acts as a simple index for the column
category_id. And, another bonus, if you are looking for all numeric data
and all of the data you want actually takes part in an index, the
optimizer will skip reading the actual table file and return the data for
the query from the index file. This is called a "covering index" for your
query. The restriction on all numeric data may be lifted with recent
developments, I haven't keep up with that aspect fo the design changes
(sorry!).
To summarize: If you define a 3-column key that looks like (A, B, C) that
definition performs as an index for the following combinations of terms: A
(by itself), A and B at the same time, A and B and C at the same time. Any
index (PRIMARY KEY, KEY, or UNIQUE) provides this functionality. That is
why I keep trying to point out that certain KEYs (indexes) that you define
are redundant because you, for example, listed a column first in a
compound PK then created a separate simple key on just that column. The PK
will do both jobs. No need to use the space to re-index that column by
itself.
<snip>
>
> >Shawn Green
> >Database Administrator
> >Unimin Corporation - Spruce Pine
> >
> >
> Thanks Shawn!
>
> -afan
>
>
Any time!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
