MySQL Forums
Forum List  »  MyISAM

Almost whole table in single index: is there a better way?
Posted by: Andy Deakin
Date: August 25, 2012 07:33AM

I have have a MyISAM table which collects live data (e.g. wind speed, temperature) and other data from remote data loggers. Each data logger has an unique RemoteID, and each type of data (e.g. wind speed) has a specific DataID. The time SensorTime is stored as a unix timestamp. To improve performance, the table is partitioned by month, using the unix timestamps. There are around 250 million rows added per month, and the data is kept for 12 months by dropping the partition which is 12 months old.
Value is a char(8) because sometimes the data might not be numeric, and I wanted a fixed row length.

A typical query might try to plot a weeks worth of data for a single sensor, and looks like:
SELECT SensorTime,Value FROM RemoteStatuses WHERE RemoteID = X AND DataID = Y AND SensorTime BETWEEN A AND B

Data is regularly inserted in batches of around 100 in a single "INSERT IGNORE" statement.

CREATE TABLE `RemoteStatuses` (
`RemoteID` int(11) NOT NULL,
`SensorTime` int(11) NOT NULL,
`DataID` smallint(6) NOT NULL,
`Value` char(8) NOT NULL,
PRIMARY KEY (`RemoteID`,`DataID`,`SensorTime`)
/*!50100 PARTITION BY RANGE (SensorTime)
(PARTITION p201208 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-08-01 00:00:00') ),
PARTITION p201209 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-09-01 00:00:00') ),
PARTITION p201210 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-10-01 00:00:00') ),
PARTITION p201211 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-11-01 00:00:00') ),
PARTITION p201212 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-12-01 00:00:00') ),

Indexing three out of four of the columns seems a bit much, and results in large index files (MYI and MYD are comparable in size)
Can any clever person out there think of a better way of storing and indexing all three columns?

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.