MySQL table partition by month

I have a huge table that stores many tracked events, such as a user click.

The table is already in the 10's of millions, and its growing larger everyday. The queries are starting to get slower when i try to fetch events from a large timeframe, and after reading quite a bit on the subject i understand that partitioning the table may boost the performance.

What i want to do is partition the table on a per month basis.

I have only found guides that show how to partition manually each month, is there a way to just tell MySQL to partition by month and it will do that automatically?

If not, what is the command to do it manually considering my partitioned by column is a datetime?

---

As explained by the manual: http://dev.mysql.com/doc/refman/5.6/en/partitioning-overview.html

This is easily possible by hash partitioning of the month output.

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
    ENGINE=INNODB
    PARTITION BY HASH( MONTH(tr_date) )
    PARTITIONS 6;

Do note that this only partitions by month and not by year, also there are only 6 partitions (so 6 monhts) in this example.

And for partitioning an existing table (manual: https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html):

ALTER TABLE ti
    PARTITION BY HASH( MONTH(tr_date) )
    PARTITIONS 6;

Querying can be done both from the entire table:

SELECT * from ti;

Or from specific partitions:

SELECT * from ti PARTITION (HASH(MONTH(some_date)));

---

使用具有独立于表大小的访问时间的TokuDb。

链接地址: http://www.djcxy.com/p/75032.html

上一篇: 简单的scalatest项目不会被编译

下一篇: 按月份MySQL表格分区