Problems with using memory tables and hash indexes
I've run into an odd problem that I can't seem to figure out or find similar problems. I have a table that I want to use the MEMORY storage engine and it works fine as long as I limit to around 11M rows. Here's what I'm seeing on my end. If I load the data from a file into the memory engine with less than 11M rows, the data load takes around 7 minutes. When I try to load a dataset with 13M rows as soon as it gets up to around 11.7M rows, the loading of the data slows down to a crawl and just keeps getting slower and slower. Does someone know of any limitations of using the HASH indices in the MEMORY storage engine? If I change all of my indices over to a BTREE index, the data load for all 13M takes about 20 minutes, but it's a consistent performance. The machine I'm running on has 32G of RAM, Solaris 10, the Mysql Max 5.0.27 64bit. I've tried tweaking the key_buffer, max_heap_table_size, tmp_table_size variables, but none of them seem to make a difference. For reference, here is the DDL for the table I'm referring to:
CREATE TABLE `db`.`device_index_stage` (
`device_index_id` int(10) NOT NULL auto_increment,
`parent_index_id` int(10) default NULL,
`device_index` int(23) default NULL,
`device_mac` varchar(17) default NULL,
`us_index` int(8) default NULL,
`device_ip` varchar(128) default NULL,
`device_state` int(1) default NULL,
`device_master_id` int(5) default NULL,
`updated_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY USING HASH (`device_index_id`),
UNIQUE KEY `device_index_stage_mac_master` USING HASH (`device_mac`,`device_master_id`),
KEY `device_index_stage_parent_index_id` USING HASH (`parent_index_id`),
KEY `device_index_stage_device_index` USING HASH (`device_index`),
KEY `device_index_stage_device_mac` USING HASH (`device_mac`),
KEY `device_index_stage_device_ip` USING HASH (`device_ip`),
KEY `device_index_stage_device_master_id` USING HASH (`device_master_id`),
KEY `device_index_stage_updated_time` USING HASH (`updated_time`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1;