I have a large table which contains sensor data, along with the fields:
I do queries against it using these fields almost exclusively. There are multiple sensors, and different sensors might have the same timestamp for a given set of data, so neither index can be unique.
I created three indexes: one for each of these columns (not unique), and a compound one for both (unique).
The table is constantly being written to, and queries to read data seem increasingly slow.
My question is, is the compound index unnecessary? Would it be faster to have only the two separate indexes? (Or remove those and keep only the compound index?) No other columns are used for filtering query data.
My question is similar to this one: Do I need separate indexes for each type of query, or will one multi-column index work?
Here's the table definition:
CREATE TABLE `data` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `sensor_id` int(10) unsigned NOT NULL, `timestamp` datetime NOT NULL, -- 71 other columns... PRIMARY KEY (`id`), UNIQUE KEY `IX_sensor_timestamp` (`sensor_id`,`timestamp`), KEY `IX_timestamp` (`timestamp`), KEY `IX_sensor_id` (`sensor_id`) ) ENGINE=InnoDB AUTO_INCREMENT=78542614 DEFAULT CHARSET=latin1