MySQL partition by month

Question

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

The table is already at 10 million, and it is growing every day. Queries begin to slow down when I try to extract events from a large timeframe, and after reading a little on this subject, I understand that splitting a table can improve performance.

What I want to do is split the table by month.

I just found manuals that show how to split manually each month, is there a way to just tell MySQL about split by month and it will do it automatically?

If not, then what is the manual command for this, given that my column-partitioned datetime number?

Answer 1

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

This is easily possible using a hash of the weekend.

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

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

And to partition 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; 

The query can be executed as from the whole table:

 SELECT * from ti; 

Or from certain sections:

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

Answer 2

Use TokuDb, which has access time regardless of the size of the table.