Re: Significance of Primary Keys

[Rick James]

In MyISAM...
* PRIMARY KEY is essentially identical to UNIQUE KEY
* A PRIMARY KEY is not required; no big harm if a table is missing one.
* It is even possible to have a MyISAM table with absolutely no indexes; that won't be much different than just writing the rows to a file.
* Data is inserted at the end of the .MYD (unless there are holes due to DELETEs/UPDATEs).
* Each index (including the PK, if any) is a BTree of 1KB blocks in the .MYI file.
* INSERTing a row involves updating every index
* Data blocks are cached by the OS; index blocks are cached in the key buffer (of size key_buffer_size).
* Indexes (incl the PK) are often useful for performance. A table of a few rows won't perform much differently with or with indexes. A table of a million rows should (probably) have index(es) (PK and/or otherwise).
* AUTO_INCREMENT needs a PRIMARY KEY or UNIQUE KEY to make it work correctly.
* You _should_ include a PRIMARY KEY when appropriate -- it is a form of documenting the structure of the table.
* With MyISAM, indexes are for performance; they have no use for transactions or foreign key constraints, since MyISAM does not support those.

Most of these comments do _not_ apply to InnoDB.