Downsize Growing Time-Related Mysql Table

Question

We have a database with the time related data in it. As you can imagine it growths (and slows down) with time. There is 50% read and 25% inserts and 25% update action on the present (this months) data, 100% read on the older data.

• The good thing is, the older data also becomes less important.
• The bad thing is that sometimes we need to query a whole period from present back to last year.

Now I want to have a mysql architecture, that serves the younger data faster than the older.

Is there a way to do that in mysql?

---

post scriptum: Of course, as we're working with ruby on rails and active record in the application layer, we could rewrite easily the active record base class to access multiple tables and move the older data to an other table. BUT because we have also read queries from other systems, like reporting, which should be able to access the old and the new data, and sometimes both at the same time, I would like to solve it on mysql.

Answer 1

MySQL partitioning was pretty much made just for you. It does require you to be on 5.1.

Answer 2

I'd split the table in to (at least) 2 tables and store current data in one and archival data it the other. Then use a MySql view to create a "virtual" table that could be used when all the data is required, otherwise, access the required data directly using the actuall table.