Partition mysql table based on months-CodePudding

I'm newbie in MySql. Supposed I have a table like this:

**Month table**
-----------------
id  month data
1    1    0.5
2    1    0.8
3    2    0.12
4    2    0.212
5    2    1.4
6    3    5.7
7    4    6.8

How can I split it into different tables based on month and have those tables available in disk. If that's possible, how can I later refer to these tables later. For example:

**January table**
-----------------
id   month data
1     1     0.5
2     1     0.8

**February table**
-----------------
id   month  data
1     2     0.12
2     2     0.212
3     2     1.4

**March table**
-----------------
id   month  data
1     3     5.7

**April table**
-----------------
id   month  data
1     4     6.8

etc.

Thank you for your help.

I looked into partition by range but I don't think it serves my purpose because it is not creating smaller tables. I was thinking of writing a procedure but don't know where to start.

CodePudding user response：

If your intent is to simply speed up queries by month, just add an index to your month or timestamp column. Partitions speed things up, but they can also slow things down.

I looked into partition by range but I don't think it serves my purpose because it is not creating smaller tables.

This is incorrect.

Partitioning takes this notion a step further, by enabling you to distribute portions of individual tables across a file system according to rules which you can set largely as needed. In effect, different portions of a table are stored as separate tables in different locations.

You can alter your existing table into 12 partitions by month. If you already have a month column...

alter table month_table
partition by list(month) (
  partition January values in (1),
  partition February values in (1),
  -- etc ---
);

And if you have a date or timestamp column, turn it into a month.

alter table month_table
partition by list(month(created_at)) (
  partition January values in (1),
  partition February values in (1),
  -- etc ---
);

...how can I later refer to these tables later

Generally, you don't refer to the individual partitions. You insert and query the main table. The point of partitioning is to be transparent. If there is an applicable where clause that will read from the related partition.

-- This will read from the February partition.
select * from month_table where "month" = 2;

-- This will read from both the February and March partitions.
select * from month_table where "month" in (2,3);

But you can query the individual partitions with a partition clause using the name of the partition.

SELECT * FROM month_table PARTITION (January);

You generally do not need to do this except to debug what is in each partition.