Re: What should be my PK?

[Rick James]

For a MyISAM-only system, key_buffer_size should be set to about 20% of available RAM.

In MyISAM, the PRIMARY KEY is no different than a UNIQUE KEY.

You have provided one query "SELECT ... WHERE URI_CM = 'constant'" -- That needs INDEX(uri_cm). If you would care to provide the other SELECTs, UPDATEs, and DELETEs, we can advise on what other indexes you might need.

That particular query operates as follows:

1. Drill down the BTree index for URI_CM (in the file book_tbl.MYI). (It might be about 5 levels deep.)
2. At the bottom will be a pointer to the row.
3. Seek into book_tbl.MYD, using that pointer as a byte offset into the file.
4. Read the record.

9 billion rows is a lot, probably a few terabytes? Much more than can be cached in RAM? So, unless you have touched that particular row 'recently', some of those 6 (5 in index + 1 in data) disk hits won't be cached. I suspect the query for some random URI_CM will take a few tens of milliseconds.

Note that I did not mention the PK in this discussion of how MySQL will find the row.

There are lots of differences if we are talking about InnoDB. The short answer is to make URI_CM the first column in the PK, and add enough columns to make the combination unique. (Then do not have INDEX(URI_CM).) I would expect 'similar' speed. The my.cnf settings would need to be different.