I have a table that has an archive of daily weather conditions around the globe. There are around 150 million rows and the table is 1.7Gb excluding indexes.
The table is an archive so there are no writes to it. It is a MyISAM table.
Queries are showing up in my slow log as taking up to 45 seconds. These are well cached by the website, but this seems excessive.
CREATE TABLE `archive` ( `latitude` float NOT NULL, `longitude` float NOT NULL, `condition` decimal(3,1) NOT NULL, `month` tinyint(4) NOT NULL DEFAULT '0', `day` tinyint(4) NOT NULL DEFAULT '0' ) ENGINE=MyISAM DEFAULT CHARSET=utf8; ALTER TABLE `archive` ADD KEY `longitude` (`longitude`), ADD KEY `month` (`month`);
I have indexes on longitude and month (as I usually run a monthly average for a location). Just to note - I can't have a primary index on just longitude and latitude as these are all repeated for each day and month.
Here is an example of one of the slow queries (actually the only query type):
SELECT AVG(condition) AS average, MIN(condition) AS min_temp, MAX(condition) AS max_temp FROM `archive` WHERE `latitude`=42.75 AND `longitude`=132.75 AND `month`=4;
64Gb RAM key_buffer_size= 256M
Any thoughts on how I can speed things up? I have considered breaking the table up but am not sure on the best way forward.