In another question I mentioned that I have a table where, in order to ensure uniqueness, I need to use multiple columns as the PK. However, lookups are usually only done using the first of those columns.
The table will ultimately have as many as hundreds of millions of rows, so it was pointed out that an auto-increment PK might be better and my multi-column PK would be less performant. Inserts can be several per second.
E.g. please ignore questions about columns, this is just an example.
CREATE TABLE records( record_code VARCHAR(20) NOT NULL, record_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, user VARCHAR(30) NOT NULL, other_data VARCHAR(100), other_data VARCHAR(100), PRIMARY KEY (record_code, record_date, user) ) ENGINE=InnoDB;
CREATE TABLE records( id INT UNSIGNED NOT NULL AUTO_INCREMENT, record_code VARCHAR(20) NOT NULL, record_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, user VARCHAR(30) NOT NULL, other_data VARCHAR(100), other_data VARCHAR(100), PRIMARY KEY (id), KEY record_code (record_code) ) ENGINE=InnoDB;
It made sense to me that an AUTO_INCREMENT surrogate PK would save disk/memory space and allow faster inserts, but another commenter disagreed, so I am wondering if it's OK to use the multi-column PK, is it just as fast? Is there not much difference?
NOTE it is possible at some point that may need to start doing lookups by both the first AND second columns, so in the second example above the second index would be
KEY record_code_date (record_code, record_date) so inserts are doing almost as much work as with just using the multi-column PK(?). Does this change the advisable approach?