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: <Alec.Cawley@stripped>
To: <vegelin@stripped>
Subject: Re: How to avoid redundancy between PK and indices ?
> "C.R. Vegelin" <vegelin@stripped> 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
