New answers tagged architecture
If you choose not to use built-in partitioning you can roll your own by having a view create view capture_table as select * from data_table_2014-01-1 UNION ALL select * from data_table_2014-01-2 UNION ALL select * from data_table_2014-01-3 etc. Overnight maintenance creates a new data table, drops the old ones and re-generates the definition of the view. ...
In my opinion, you should use an SSD if you are facing performance problems. Here is a 120 GB drive for approx. 64 Euro (< 100$ US). This will keep your application ticking over for a couple of years without any need for a redesign and performance should substantially improve for minimum expense. Down the road, if you are ultimately deciding to move to a ...
My opinion since you are going to have multiple VMs and you want to stick with Mysql use Fabric to shard your database. http://dev.mysql.com/doc/mysql-utilities/1.4/en/fabric-quick-start-sharding-scenario.html. Sharding is horizontal partitioning and you can split your data across multiple servers and gain faster writes and reads.
Top 50 recent answers are included