Hi Hank,
You are quite right.
I need separate non-unique indices on a, b, c, d, e and f to avoid table
scans.
And when each combi must be unique I need a Primary Key (a,b,c,d,e,f).
And only Key a (a) seems to be redundant with the primary key ...
Suppose there would be a PK (a,b,c,d,e,f) defined, without a "separate" PK
index.
And let's assume some rows like:
columns: a b c d e f
row1 has: 1 1 1 1 1 1
row2 has: 1 1 1 1 1 2
row3 has: 1 1 1 1 1 3
etc.
Then checking on unique PK could be done by MySQL internally with:
Select Count(*) From myTable Where a=1 And b=1 And c=1 And d=1 And e=1 And
f=1;
to avoid a duplicate primary key for row1, by using / joining the separate
"index tables".
With this Select query, MySQL could / should make use of the 6 existing
separate indices.
Uniqueness can be fully guaranteed with these 6 non-unique indices in this
case.
In other words, a separate PK index is fully redundant in this case, right ?
In addition, it would save space without the longer concatenate key of
a+b+c+d+e+f.
Thanks, Cor
----- Original Message -----
From: "Hank" <[EMAIL PROTECTED]>
To: "C.R. Vegelin" <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Wednesday, October 05, 2005 5:57 PM
Subject: Re: How to avoid redundancy between PK and indices ?
It depends.. if this is your create table statement:
CREATE TABLE foo (
a smallint NOT NULL,
b smallint NOT NULL,
c smallint NOT NULL,
d smallint NOT NULL,
e smallint NOT NULL,
f smallint NOT NULL,
PRIMARY KEY (a,b,c,d,e,f)
);
Then only one unique index is being created on the concatenate key of
a+b+c+d+e+f. Queries on any fields other than A will cause a full
table scan.
On the other hand, if your create table is:
CREATE TABLE foo (
a smallint NOT NULL,
b smallint NOT NULL,
c smallint NOT NULL,
d smallint NOT NULL,
e smallint NOT NULL,
f smallint NOT NULL,
PRIMARY KEY (a,b,c,d,e,f),
KEY a (a),
KEY b (b),
KEY c (c),
KEY d (d),
KEY e (e),
KEY f (f)
);
This will create the primary key, plus six additional indexes, each of
which is queryable. But in this case, the "KEY a (a)" non-unique index
is redundent with the primary key, so to do what you want - a unique
index on a+b+c+d+e+f PLUS the ability to independtly search the b c d
e and f fields, here is the create table you'll need to use:
CREATE TABLE foo (
a smallint NOT NULL,
b smallint NOT NULL,
c smallint NOT NULL,
d smallint NOT NULL,
e smallint NOT NULL,
f smallint NOT NULL,
PRIMARY KEY (a,b,c,d,e,f),
KEY b (b),
KEY c (c),
KEY d (d),
KEY e (e),
KEY f (f)
);
--
-Hank
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
