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

[Andy Deakin]

Thank you for your reply. I am using MySQL 5.1.49-3

EXPLAIN SELECT SensorTime,Value FROM RemoteStatuses WHERE SensorID = 525 AND DataID = 110 AND SensorTime BETWEEN 1346083196-86400*7 AND 1346083196
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	RemoteStatuses 	range 	PRIMARY 	PRIMARY 	10 	NULL 	8309 	Using where

EXPLAIN PARTITIONS SELECT SensorTime,`Value` FROM RemoteStatuses WHERE SensorID = 525 AND DataID = 110 AND SensorTime BETWEEN 1346083196-86400*7 AND 1346083196
id 	select_type 	table 	partitions 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	RemoteStatuses 	p201209 	range 	PRIMARY 	PRIMARY 	10 	NULL 	8304 	Using where

I did some benchmarks using InnoDB, and found that the database file was substantially larger (more than double) than the MyISAM. The access speed was comparable in the tests that I carried out. I also had concerns about disk space not being freed up when the old data/partitions are pruned with InnoDB. That being said, I do like the idea of not waiting a long time for tables to check/repair themselves when mysql crashes and restarts.