Question: Does anyone have any recommendations that are likely to keep a large database healthy/speedy?
I'm designing a database that is going to retrieve around thirty million data points a year, up to around three hundred million to a billion in a few years time when all of the sensors come on line. The structure of the table containing all of the data is:
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| feed_id | int(11) | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
| value | double | NO | | NULL | |
| creator_id | int(11) | NO | MUL | NULL | |
| date_created | datetime | NO | | NULL | |
+--------------+----------+------+-----+---------+----------------+
The data is going to be retrieved by date and feed_id (I've made both of those indexes) and data more than three or so years back will likely be fairly useless, but probably won't be deleted.)
I've heard partitioning could be useful, does this work with multiple indexes and/or how useful is it? I've seen questions elsewhere asking about ~10,000,000 rows, and it seems MySQL is fine for that amount; should I look elsewhere for a dataset this large?
Thanks,
joehms22