how to partition a table by datetime column?

by

Partitions by HASH is a very bad idea with datetime columns, because it cannot use partition pruning. From the MySQL docs:

Pruning can be used only on integer columns of tables partitioned by
HASH or KEY. For example, this query on table t4 cannot use pruning
because dob is a DATE column:

SELECT * FROM t4 WHERE dob >= '2001-04-14' AND dob <= '2005-10-15';

However, if the table stores year values in an INT column, then a
query having WHERE year_col >= 2001 AND year_col <= 2005 can be
pruned.

So you can store the value of TO_DAYS(DATE()) in an extra INTEGER column to use pruning.

Another option is to use RANGE partitioning:

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 p20110403 VALUES LESS THAN (TO_DAYS('2011-04-04')),
    PARTITION p20110404 VALUES LESS THAN (TO_DAYS('2011-04-05')),
    ...
    PARTITION p20110426 VALUES LESS THAN (TO_DAYS('2011-04-27')),
    PARTITION p20110427 VALUES LESS THAN (TO_DAYS('2011-04-28')),
    PARTITION p20110428 VALUES LESS THAN (TO_DAYS('2011-04-29')),
    PARTITION p20110429 VALUES LESS THAN (TO_DAYS('2011-04-30')),
    PARTITION future VALUES LESS THAN MAXVALUE
  );

Now the following query will only use partition p20110403:

SELECT * FROM raw_log_2011_4 WHERE ftime="2011-04-03";

More Related Contents:

  1. Should I use the datetime or timestamp data type in MySQL?
  2. How do you set a default value for a MySQL Datetime column?
  3. MySQL Query GROUP BY day / month / year
  4. What column type/length should I use for storing a Bcrypt hashed password in a Database?
  5. What data type to use for hashed password field and what length?
  6. How to group by week in MySQL?
  7. Difference between two dates in MySQL
  8. MySQL Query to select data from last week?
  9. MySQL convert date string to Unix timestamp
  10. Mysql: Setup the format of DATETIME to ‘DD-MM-YYYY HH:MM:SS’ when creating a table
  11. How to store a datetime in MySQL with timezone info
  12. How to select date from datetime column?
  13. Datetime equal or greater than today in MySQL
  14. How to convert number of week into date?
  15. Group mysql query by 15 min intervals
  16. SQL Query to show nearest date?
  17. How to subtract hours from a datetime in MySQL?
  18. Storing SHA1 hash values in MySQL
  19. How to convert time to the time zone of the iPhone device?
  20. Select data between a date/time range
  21. mysql: get record count between two date-time
  22. MySQL compare DATE string with string from DATETIME field
  23. How to cast DATETIME as a DATE in mysql?
  24. MySQL – select data from database between two dates
  25. How to compare timestamp dates with date-only parameter in MySQL?
  26. Get total hours worked in a day mysql
  27. MySQL select all rows from last month until (now() – 1 month), for comparative purposes
  28. MySQL: Insert datetime into other datetime field
  29. MySQL – How to parse a string value to DATETIME format inside an INSERT statement?
  30. Working with INTERVAL and CURDATE in MySQL

Leave a Comment