My site has a main MySQL InnoDB table that it does most of its work on. New rows get inserted at a rate of 1 million per week, and rows older than a week gets moved over to an archive table on a daily basis. These archived rows are processed once a week for stuff like finding trends.

This archive table consequently grows at 1 million new rows every week, and querying it can get really slow. Is MySQL suited for archiving data, or is my strategy very flawed?

Please advise, thank you!

  • While you can archive data in mysql. You may need to transfer/transform the data to another type of store if you want to query it efficiently. Your options largely depend on your data and what you want to do with it. – datasage Mar 6 '13 at 20:25
  • Do you have an example of transforming the data to another type of store? – Nyxynyx Mar 6 '13 at 20:27
  • 2
    Columnar stores come to mind, like infinidb or amazon's redshift. These types of stores, store data by column instead of row. Which works well for doing adhoc queries on large data sets. – datasage Mar 6 '13 at 20:30
  • There are some good recommendations for options other than MySQL here, which as everyone's said, isn't ideal for this use case. I'll recommend another, the Infobright engine. I have seen this in action, and it does a very efficient job of storing and also retrieving data from large "archived" datasets. – Valerie Parham-Thompson Mar 7 '13 at 13:22

I would be very tempted to store in a no-SQL data store, like Mongo or Couch. Writes are incredibly fast, scales well, etc.

You might even archive in a mongo collection, then store "processed" results in an RDBMS, which you can then query very quickly with SQL.

To stay in MySQL, you're looking at some sort of partitioning scheme to get this to scale at all.

  • Mongo is not a good option if you want to do adhoc queries. Would be fine for archival though. – datasage Mar 6 '13 at 20:31
  • 1
    Which is precisely why I recommended it for that. SQL is good at reporting; No-SQL is good at logging. – landons Mar 6 '13 at 20:32

If you have similar amount of data coming every week and you have to run queries on large set of data. Be it any store, it will take some time based on the size of data and the kind of query you are doing on that data. As extracting trends might want you to run complex aggregations.

You might have to do sharding as well when the data will become really huge on mysql.

I would suggest to look at an option of having this data stored in hadoop and then query the same using hive, which gives capabilities of adhoc SQL queries. Which can run on very large data but again it cant be just real time. But will give you huge scalable solution for archival

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.