Re: PK or simple key?

[Jigal van Hemert]

Eamon Daly wrote:

I don't know why I have so much trouble visualizing indexes,
but such is life. Imagine a simple table with only two
columns: user_id and foo_id. I will only ever be looking up
records in this table by user_id, but I will always retrieve
both fields. Figure a million rows and maybe 5 or so rows
per user_id.

Should the table be defined with a PK across all columns:
or should the table have no PK and a key on user_id:

Eamon,



Kind regards, Jigal.

With only two integer fields and MyISAM it might be a solution to use a PK on both fields. In that case MySQL only needs to access the index to retrieve the data also. The actual table data will not be used, but as long as the entire index fits in memory it will be super fast.

smime.p7s
Description: S/MIME Cryptographic Signature