MySQL Forums
Forum List  »  Partitioning

500 mil. rows table partitioning
Posted by: Misha Krassovski
Date: June 27, 2011 09:15AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
500 mil. rows table partitioning
3682
June 27, 2011 09:15AM


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.