How Can I Efficiently Partition a Table by a Datetime Column in MySQL?

How to Partition a Table by Datetime Column Efficiently

Partitioning tables by date columns is a technique used to improve query performance. However, it's important to understand the limitations and choose the appropriate partitioning method.

When partitioning by hash, as in your example, it's not possible to use partition pruning for date columns. In such cases, you can create an additional INTEGER column storing the result of TO_DAYS(DATE()) and then partition by that column. This allows for efficient partitioning and data retrieval.

Another approach is to use RANGE partitioning. By partitioning the table into defined date ranges, you can ensure that specific date ranges are located in specific partitions. This approach provides better pruning and performance for date-based queries.

For example, you could create the following RANGE partitioned table:

<code class="sql">CREATE TABLE raw_log_2011_4 (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  logid char(16) NOT NULL,
  tid char(16) NOT NULL,
  reporterip char(46) DEFAULT NULL,
  ftime datetime DEFAULT NULL,
  KEY id (id)
) ENGINE=InnoDB AUTO_INCREMENT=286802795 DEFAULT CHARSET=utf8
  PARTITION BY RANGE( TO_DAYS(ftime) ) (
    PARTITION p20110401 VALUES LESS THAN (TO_DAYS('2011-04-02')),
    PARTITION p20110402 VALUES LESS THAN (TO_DAYS('2011-04-03')),
    ...
    PARTITION future VALUES LESS THAN MAXVALUE
  );</code>

With this RANGE partitioned table, the following query will efficiently retrieve data for the specified date:

<code class="sql">SELECT * FROM raw_log_2011_4 WHERE ftime = '2011-04-03';</code>

The above is the detailed content of How Can I Efficiently Partition a Table by a Datetime Column in MySQL?. For more information, please follow other related articles on the PHP Chinese website!