Hi Alec,
Thanks for your comment. Well, we disagree on a few points.
Suppose I have a table with columns CountryID, CompanyID, SectorID and
ProductID.
And let's say that all these columns are NOT NULL, but indexed as
non-unique.
I need to select on specific countries, specific products etc.
So I need 4 separate indices, where CountryId may occur more than once in
the CountryId index,
CompanyID may occur more than once in the CompanyID index etc.
But if these 4 columns together are defined as Primary Key, then each combi
of CountryID, CompanyID, SectorID and ProductID is unique.
In my point of view this can only be realized with a separate PK index,
leading to redundancy in the indices.
My theory is backed by what I read in the manual ... if I read it right ...
If you use ALTER TABLE on a MyISAM table, all non-unique indexes are created
in a separate batch.
ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes
for a MyISAM table.
ALTER TABLE ... ENABLE KEYS then should be used to re-create missing
indexes.
These 2 features can only be realized if MySQL uses separate indices for
non-uniques and for PK's.
Don't you think ?
Regards, Cor
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Subject: Re: How to avoid redundancy between PK and indices ?
"C.R. Vegelin" <[EMAIL PROTECTED]> wrote on 04/10/2005 12:52:01:
Hi List,
Is anyone familiar with optimizing indices, including primary key ?
I do have a large myisam table with 6 non-unique key fields, lets
say named A, B, C, D, E and F.
Each of these columns may have NOT NULL values from 0 to 999, and
are defined as SmallInt.
Requirement: each row must have a unique combination of these 6 key
fields (all Btree) !
To force uniqueness on this table, I can define a PRIMARY KEY (A, B,
C, D, E, F);
But I suppose that MySQL makes a separate (physical) index for the
primary key, besides the 6 member indices.
I do not think this is true. If you specify an index, be it primary or
not, there is only one index. As you describe it, none of the separate
columns is a candidate for a primary key, visible or otherwise, because
none of them is of itself unique.
And apart from uniqueness, this primary key does not have any added
value for programming purposes. Right ?
I believe that if you have InnoDB tables, searching by the primary key is
likely to be significantly faster than searching by secondary keys.
My question: does MySQL allow some kind of virtual primary key,
where uniqueness is enforced by MySQL by checking its member indices ?
In MyISAM tables, the Primary key, or UNIQUE keys have no other function
than this. In structure, a primary key is no different to any other key.
At this moment my table has more than 13 million rows (about 1100 MB
Data_Length).
And the Index_Length is about 500 MB, for the 6 indices and the
primary key, consisting of these 6 indices.
In this case a virtual primary key could save maybe 200 MB in stead
of a real primary key index and could speed up the updating processes.
I like to hear from you.
I think you are wrong in your presumption that there are individual
indexes. Consider a telephone directory: this may be regarded as indexed
on FamilyName, GivenName. There is only one index even though it is over
two fields (the order in the telephone directory). You would only need
another index if you wanted to search over GivenName,FamilyName. This
would then require an extra index, which would have to be put in the back.
Alec Cawley
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
