MySQL Forums
Forum List  »  MyISAM

Re: Almost whole table in single index: is there a better way?
Posted by: Andy Deakin
Date: August 27, 2012 10:18AM

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.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.