Re:How to Get the Most Out of Postgres Memory Settings
by hot_gril on Thursday June 13, 2024 @12:15PM
(#40671509)
I'm used to Postgres, which doesn't have clustered key. If this is MySQL and you're using a clustered key, pkey is still how you'd identify rows in foreign keys etc, so the same advice of bigserial pkey applies.
Primary Key serves to uniquely identify the row. This is used to reference the tow in other tables. (So the Invoice table contains the Primary Key value for the customer.)
The Clustered key describes the physical sort order on the disk. Clustering on your most common search improves performance for that search. Searching on other criteria becomes equally slow.
So, it's really common to search through invoices by date. So a date key is ideal for a clustered index on that table. Looking up all invoices for a date range is faster than all invoices by, say, customer.
Clustering the customer or employee table by date though isn't great, I don't want to often see my employees on take-on order.
We teach Primary Keys at Uni, but in my era anyway they didn't put the same emphasis on selecting a correct clustered key. So I feel like people tend to assume the Primary Key is also the clustered key.