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. And apart from uniqueness, this primary key does not have any added value for programming purposes. Right ? My question: does MySQL alllow some kind of virtual primary key, where uniqueness is enforced by MySQL by checking its member indices ? 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. Thanks, Cor
