[Resolved] 2 primary keys vs 1 primary key with index

[acrabadabra]

Something I have always wondered while working with myISAM and related tables, is the following dilemma (in what to use best):

relID, uniqueField, Name // in where relID and uniqueField are the TWO primary keys

versus

ID, relID, uniqueField, Name // in where only ID is the primary key and relID an index

Are there differences in speed or maybe someone had occurrences in problems with this format?

[MarkR]

You've got something a bit confused.

You can't have two primary keys. You CAN have a primary key which consists of more than one column.

Normally I avoid having tables with a multi-column primary key, and instead add an artificial meaningless (autonumber) ID, even if it's not used anywhere (it typically isn't).

In any case, you can have more indexes. Creating a primary key always puts a unique index on the set of columns in the PK, but it does not stop you having indexes on other sets of columns.

Indeed, if you're searching on part of the primary key, an index on the whole PK won't be useful, so you might want to add another one.

Mark

[acrabadabra]

You're right, I meant one primary key combined of two columns.

So, actually it is smarter to NOT have a seperate (bogus) primary key, since this would be used an an index value too, and of course could slow down the whole search (JOIN) procedure.

Okay, thanks for the enlighting!