Re: Almost whole table in single index: is there a better way?

[Rick James]

> I wanted a fixed row length.
There is rarely any advantage for such, especially since the rows never changes.

What is the average length of `Value`? CHAR(8) always takes 8 bytes; VARCHAR(8) would take 1+avg.

When you have billions of rows, shrinking the data becomes an important task.

If you are always querying this way:
> WHERE RemoteID = X AND DataID = Y
then you may as well 'normalize' that pair of values in another table, replacing the two columns with a SMALLINT UNSIGNED or MEDIUMINT UNSIGNED that is the id for the pair. That would shave 3-4 bytes off your 19-byte records.

> BETWEEN 1346083196-86400*7 AND 1346083196
That includes an extra second.

> dropping the partition which is 12 months old
That, alone, is worth PARTITIONing for. (There may be no other advantages in PARTITIONing. The one SELECT you showed will not run any faster.)

> batches of around 100
Good. Is there any issue with INSERT speed? (I suspect not.)

> "INSERT IGNORE" statement
'IGNORE' -- just in case you reload the data??

MyISAM will drill down the BTree in the .MYI, then do a random read into the .MYD.
InnoDB would drill down the BTree in the PK/data, and find the entire record at the leaf node.

> I also had concerns about disk space not being freed up when the old data/partitions are pruned with InnoDB
DROPping old PARTITIONs works the same in either Engine. With file_per_table, you can see the change; without file_per_table, the space in ibdata1 will get reused.

> I added innodb_file_per_table to my.cnf, but have not tested since changing it.
Be sure to recreate the table or "ALTER TABLE RemoteStatuses ENGINE=InnoDB" to make the file_per_table take effect.

With MyISAM, do
key_buffer_size = 800M
innodb_buffer_pool_size = 0

With InnoDB, do
key_buffer_size = 20M
innodb_buffer_pool_size = 2000M
innodb_file_per_table = 1

Are you running 32-bit? or 64-bit?

> RemoteStatuses MyISAM 10 Fixed 275,899,846 19 5,242,097,074 0 4,592,669,696 0 NULL 2012-03-18 00:13:59 2012-08-27 20:19:04 2012-08-24 23:20:12 latin1_swedish_ci NULL partitioned
That's just 1 month? And you plan to have 12 months?

Changing myisam_data_pointer_size from the default of 6 to 5 would shrink the index by 275MB. (3GB for a year)

> Value is a char(8) because sometimes the data might not be numeric
You would probably be better off having two tables -- one for numeric data (with a 4-byte FLOAT), and another for non-numeric (VARCHAR(8) - 1-9 bytes). The added complexity of fetching from two tables might be worth the space/speed savings.