I have a MySQL table which I expect to be in surplus of a few million rows and 99% select statements. The problem I am having is coming up with a meaningful way to determine the Primary Key. (I have provided a table dump at the bottom for reference)
For some background, I am working with a 2-D grid, whose ranges vary from approximately -800000 to +800000 in each direction. Each row is identified by its X/Z coordinate, for which each coordinate may have 1-30 associated bitstrings (type).
The concerns I have are:
1) I lack a meaningful way to Primary key this table. While I know I can create an
id field with auto_increment, I know that in practice this key will never be used, as 100% of SELECTS will be in the form of:
SELECT `type`, `offset`, `bitstring` WHERE `x` = 0 AND `z` = 0;
2) I intend to index on multiple columns (the logical x/z), via:
CREATE INDEX coordinate ON bitstrings(x, z)
While I feel this appropriately addresses my real-world selects, I am constantly concerned it is insufficient indexing, based on the dozens of posts saying 'YES, YOU NEED A PK'. Is this a case where a PK can be ignored or is the arbitrary
id still ultimately going to provide some behind-the-scenes optimization well worth the additional table size and column?
As a side note, I have absolutely no limitations on completely restructuring this table, if there is are any more practical, proven ways to store this sort of data.
CREATE TABLE `bitstrings` ( `x` int(11) NOT NULL COMMENT 'roughly +/- 10^6 range', `z` int(11) NOT NULL COMMENT 'roughly +/- 10^6 range', `type` smallint(6) NOT NULL COMMENT 'range: 1-4096', `offset` smallint(6) NOT NULL COMMENT 'range: 1-65535', `bitstring` blob NOT NULL COMMENT 'binary data len: 1-8192' ) ENGINE=MyISAM DEFAULT CHARSET=utf-8; -- -- Dumping data for table `bitstrings` -- INSERT INTO `bitstrings` (`x`, `z`, `type`, `offset`, `bitstring`) VALUES (0, 0, 1, 0, 0x52), (0, 0, 2, 1878, 0x52);