Return to Revisions

Two things

• Since range of x and z are +/- 1000000, use meduimint (Range of meduimint is +/- 8388608)
• Since ranges of offset and type is 1-65535, use smallint unsigned

Make these chnages

CREATE TABLE `bitstrings` ( 
  `x` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range', 
  `z` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range', 
  `type` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-4096', 
  `offset` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-65535', 
  `bitstring` blob NOT NULL COMMENT 'binary data len: 1-8192'
) ENGINE=MyISAM DEFAULT CHARSET=utf-8; 

When it comes to indexes, you may have a choice

#CHOICE #1 : Use a primary key of ID

Setup the table like this

CREATE TABLE `bitstrings` ( 
  `id` int unsigned not null auto_incrmenet,
  `x` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range', 
  `z` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range', 
  `type` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-4096', 
  `offset` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-65535', 
  `bitstring` blob NOT NULL COMMENT 'binary data len: 1-8192',
  primary key (id),
  key xztype (x,z,type)
) ENGINE=MyISAM DEFAULT CHARSET=utf-8; 

This could allow you to query using x and z, while retrieving all ids to have a quick reference back to the row:

SELECT id FROM bitstrings WHERE x=0 and z=0;

If you ever have to retrieve the data for a given id, you could select it as

SELECT * FROM bitstrings WHERE id = 12;

This will retrieve the specific info for whatever x, z and type are there.

#CHOICE #2 : Use x,z,type as the PRIMARY KEY

Run this query

SELECT COUNT(1) rcount,x,z,type FROM bitstrings GROUP BY x,z,type HAVING COUNT(1) > 1;

If this query comes back with no rows at all, this can be your primary key

CREATE TABLE `bitstrings` ( 
  `x` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range', 
  `z` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range', 
  `type` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-4096', 
  `offset` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-65535', 
  `bitstring` blob NOT NULL COMMENT 'binary data len: 1-8192',
  primary key (x,z,type)
) ENGINE=MyISAM DEFAULT CHARSET=utf-8; 

#CONCLUSION

Since all queries are based on mainly on x and z, either choice would be OK

• The first choice would yield a bigger .MYI file, but two ways to gather data (grouped by x,z or granularly by id).
• The second choice only gathers by x and z but a smaller .MYI file.