1

It might be a weird situation, but it just come to my mind...
Imagine I have a database table which takes 1 million new rows everyday. There are 3 columns in the table: id, value, date.

What I would like to do with the rows is to load all rows depending on date.

Here comes to the question:

Given the nature of this table and the way I use it(I only need to grab the list of rows on a specific day), performance wise, does creating a new table with same structure but named with date on daily basis(ie, create table with name 01Jan2014, 02Jan2014, ... each with 1 million records in it) takes advantage over having all rows in one table and date column as index?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Tony Fantacy
  • 65
  • 7
  • Yes. It's sharding: http://en.wikipedia.org/wiki/Shard_(database_architecture) – aconrad Jan 15 '14 at 01:37
  • Are you talking about horizontal partitioning? Say I use MySQL; does partitioning has to be done when you are creating the table, or partitions can be added to tables after it has been filled with millions of data? – Tony Fantacy Jan 15 '14 at 22:47

1 Answers1

1

There's no need to create multiple tables. You can define one table with Partitioning, so it appears to be one logical whole table, but internally it is stored as multiple physical tables with identical structure.

CREATE TABLE a_database_table (
 id INT AUTO_INCREMENT,
 date DATE NOT NULL,
 value TEXT,
 PRIMARY KEY (id, date)
) PARTITION BY RANGE COLUMNS (date) (
  PARTITION p1 VALUES LESS THAN ('2014-01-01'),
  PARTITION p2 VALUES LESS THAN ('2014-01-10'),
  PARTITION p3 VALUES LESS THAN ('2014-01-20'),
  PARTITION p4 VALUES LESS THAN ('2014-02-01'),
  PARTITION pN VALUES LESS THAN (MAXVALUE)
);

As the data gets close to the last partition (or even after it starts filling the last partition), you can split it:

ALTER TABLE a_database_table REORGANIZE PARTITION pN INTO (
  PARTITION p5 VALUES LESS THAN ('2014-02-10'), 
  PARTITION pN VALUES LESS THAN (MAXVALUE)
);

The advantage of partitioning is that a query against a specific day will "prune" its access to the table so it only reads the one relevant partition. This happens automatically if your query is specific about the day and MySQL can infer which partition contains the rows you're looking for.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Do I have to configure for Partition at the moment I create the table? or I can always add it after the table is created and there are rows in it already? – Tony Fantacy Jan 16 '14 at 03:31
  • 1
    Yes, you can use ALTER TABLE to turn an unpartitioned table into a partitioned one, even if it has a lot of data in it. But the more data, the longer it will take to do that restructure. – Bill Karwin Jan 16 '14 at 04:46