"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]
