I have a dataset that is stored across 20 or so tables, most of which have a few million rows. This data will be generated fresh each month, and compared to past datasets.

I'm pretty new to dealing with datasets of this size, so I'm unsure what would be good practice in storing these past datasets. I need the data to still be accessible to queries, so dumping the database to backup files won't be enough. Is there some feasible way to move each dataset into a new database once I get a new one? Or should I just put timestamps on each row and keep everything in one giant dataset?

Sorry if this is too vague, I'm not sure how exactly to ask this question. I'd be open to changing it if there are any suggestions.

  • You can sure store multiple months in the same set of tables. Tables storing tens of millions of rows are not unheard of and with your size of one month data you will have to index it properly anyway. One possibility is to use partitioning on bigger tables, with separate partition for one or couple of months. – jkavalik Jun 3 '15 at 19:11
  • Ok, that was the one thing I needed to hear to know where to explore: partitioning. I hadn't heard of it before, and my searches weren't using the right words to find it. If you'd like to make an answer that vaguely points to partitioning, I'd accept it to give you credit – Indigenuity Jun 3 '15 at 19:32

Extending comment to answer:

MySQL can handle lots of rows. So making separate schema for each month gives no benefits unless those datasets differ in schema definition.

With some millions of rows per month you have to design your schema and index carefully enough to make querying fast. When you do it so dataset_id (some month identifier or something) is first in primary key for each table, then (at least in InnoDB by default) your data are clustered by it, so they are kept close together in table structure and disk access is not wasted.

If some tables are going to be really big, then you can use partitioning by dataset/month (probably one partition per month, partitioning key has to be part of primary key and each unique index - not sure if it has to be last in index or anywhere..). That way each partition is like physical table, your queries always access specific dataset so can only work on one such "subtable" instead of all data, and you can easily drop old partitions to reclaim some space (probably after moving the data into some archive).

| improve this answer | |
  • Absolutely this - single schema, partitioning. – Michael Green Jun 4 '15 at 11:03
  • If you get a monthly dump of data, then do ALTER TABLE .. ADD PARTITION .. and load the data. The load must contain the "partition key" so that it knows to go into the new partition. We can go into more details if you provide SHOW CREATE TABLE (even without partitioning yet). – Rick James Jun 8 '15 at 22:20

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.