String / int columns

Found a table of ours:
hashid bigint
value_a varchar(16)
value_b varchar(16)

In MyISAM, yes, this is a dynamically sized table. Turns out, data was only integers. Altered the table to be

hashid bigint
value_a integer
value_b integer

Goes to fixed size rows which is great. Also dropped size from 411M to 350M.

Remember the golden rule:
Optimize your dataset before your hardware...

MySQL: How to use lots of memory

So obviously in a database, you want to leverage memory to reduce disk accesses.

InnoDB: Set innodb_buffer_pool_size as large as possible.

MyISAM: The plot thickens. Pre 5.0.52, you could only set key buffers to 4G. Now you can go larger. To leverage memory, I do the following, changed to >4G now that it's been fixed, concept is the same. Note: preloading the caches takes some time (it has to read the whole MYI from disk) Rebooting with this configuration is *expensive*, but server is back to 100% at reboot instead of warming up. Remove LOAD INDEX commands if that's too annoying for your purpose.

file [my.cnf]
# Initialization file
init-file = init.sql
# Global key cache
key_buffer_size = 4G
# Table based key caches:
[tablename1]_cache.key_buffer_size=4G;
[tablename2]_cache.key_buffer_size=4G;
[tablename3]_cache.key_buffer_size=4G;
[tablename4]_cache.key_buffer_size=4G;

file [init.sql]
CACHE INDEX [database1].[tablename1] IN [tablename1]_cache
CACHE INDEX [database1].[tablename2] IN [tablename2]_cache
CACHE INDEX [database1].[tablename3] IN [tablename3]_cache
CACHE INDEX [database1].[tablename4] IN [tablename4]_cache
LOAD INDEX INTO CACHE [database1].[tablename1]
LOAD INDEX INTO CACHE [database1].[tablename2]
LOAD INDEX INTO CACHE [database1].[tablename3]
LOAD INDEX INTO CACHE [database1].[tablename4]