500 mil. rows table partitioning
I have a DB that holds meteorological measurements that are performed every 30 min. The main table with actual measurements has now a little bit less than 500 mil. records and will grow. The table has the following structure:
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| extensionid | smallint(6) | NO | | NULL | |
| flag | varchar(5) | NO | | NULL | |
| offsetid | int(11) | NO | | NULL | |
| repeat | tinyint(4) | NO | | NULL | |
| siteid | int(11) | NO | | NULL | |
| time | decimal(10,6) | NO | | NULL | |
| value | float | NO | | NULL | |
| variableid | smallint(6) | NO | | NULL | |
+-------------+---------------+------+-----+---------+-------+
Most of the time data will be only read from this table, but once in a week or two, I have to add new data to it. Read queries look like this:
SELECT 'US-IB2' AS site, t0.time, t0.value AS APAR_0, t1.value AS APAR_fill_0,
t2.value AS APARpct_0, t3.value AS APARpct_fill_0, t4.value AS FH2O_0, t5.value AS FH2O_fill_0
FROM L2data_part t0 INNER JOIN L2data_part t1 ON t0.time = t1.time
INNER JOIN L2data_part t2 ON t0.time = t2.time
INNER JOIN L2data_part t3 ON t0.time = t3.time
INNER JOIN L2data_part t4 ON t0.time = t4.time
INNER JOIN L2data_part t5 ON t0.time = t5.time
WHERE t0.siteid = 47 AND t0.variableid = 1 AND t0.repeat = 0
AND t0.extensionid = 3 AND t0.offsetid = 3 AND t0.flag = 0 AND t1.siteid = 47
AND t1.variableid = 1 AND t1.repeat = 0 AND t1.extensionid = 1
AND t1.offsetid = 3 AND t1.flag = 0 AND t2.siteid = 47
AND t2.variableid = 6 AND t2.repeat = 0 AND t2.extensionid = 3
AND t2.offsetid = 3 AND t2.flag = 0 AND t3.siteid = 47
AND t3.variableid = 6 AND t3.repeat = 0 AND t3.extensionid = 1
AND t3.offsetid = 3 AND t3.flag = 0 AND t4.siteid = 47
AND t4.variableid = 5 AND t4.repeat = 0 AND t4.extensionid = 3
AND t4.offsetid = 3 AND t4.flag = 0 AND t5.siteid = 47
AND t5.variableid = 5 AND t5.repeat = 0 AND t5.extensionid = 1
AND t5.offsetid = 3 AND t5.flag = 0 ORDER BY t0.time;
i.e. at any given time only one siteid is queried, but number of variableids can be up to 83 (only 6 in this case). Having all this I have two questions:
1. Would be MyISAM with partitioning by siteid a good choice?
2. What is the best way to set up indexes on this table?
Thank you in advance,
Misha