I agree that if you want to do the searches you describe, you will need
extra keys. But MySQL does *not* create these keys automatically - it
creates the keys you ask for *and no more*. You have asked, correctly, for
a primary key on ABCDEF. MySQL will create exactly that key and no others.
This will allow you to search on A, AB, ABC etc. If you want a separate
search on B, or on D, or on F, *you* must request individual indexes on
these columns. MySQL does not implicitly create hidden indexes for you;
there is exactly one index for each PRIMARY KEY/UNIQUE/INDEX. This, of
course, produces redundancy; but only the redundancy that you request.
Without these redundant indexes, a search on B will be forced to do a full
table scan. And you *need* those indexes to do the searches you want.
Without them, all searches become full table scans. And without a PRIMARY
KEY index, every insert would have to include a full table scan.
"C.R. Vegelin" <vegelin@stripped>
Re: How to avoid redundancy between PK and indices ?
Thanks for your comment. Well, we disagree on a few points.
Suppose I have a table with columns CountryID, CompanyID, SectorID and
And let's say that all these columns are NOT NULL, but indexed as
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
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
in a separate batch.
ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique
for a MyISAM table.
ALTER TABLE ... ENABLE KEYS then should be used to re-create missing
These 2 features can only be realized if MySQL uses separate indices for
non-uniques and for PK's.
Don't you think ?
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
> 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
>> 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
> Alec Cawley
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql