There is abundant documentation about the partitioning feature in MySQL 5.1. However, I thought it would be helpful to have a quick “how-to” for date-based partitioning. I will use the world schema (available at http://dev.mysql.com/doc/index-other.html), so it’s easier to follow my examples.
Partitioning a table by a date range is quite popular. Unfortunately, the RANGE partition type only accepts an integer (or a function that returns an integer) as the expression that determines the partition creation. This is great if you want to partition numeric types like this:
ALTER PARTITION BY RANGE City TABLE (id)
(
PARTITION p0 VALUES LESS THAN (1000),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN (3000),
PARTITION p3 VALUES LESS THAN (4000),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
IMPORTANT NOTE: When using RANGE partitioning, partitions must be defined using “less-than” values, and the ranges must be listed in ascending order.
If you prefer to partition a DATE or DATETIME column, things aren’t as simple as the example above. You cannot use a date directly (for example, this syntax will produce an error: date_column (range) .. PARTITION p0 VALUES LESS THAN ('2000-01-01')). There are several possible solutions, but my favorite is to use the TO_DAYS function.
First, I need a DATE column, so let’s add one to the City table:
ALTER TABLE ADD City citydate DATE;
# Now, how about filling the newly created column with random dates from the past 3 years? ~
UPDATE City SET citydate = current_date – INTERVAL truncate(rand()*1000,0) DAY;
# Remove the partitioning (done at the beginning of the text) from the table City
ALTER TABLE City REMOVE PARTITIONING;
# Remove the PRIMARY KEY and Replace it with another index (I will explain below)
ALTER TABLE DROP PRIMARY KEY City, ADD INDEX (id);
# Partition by column citydate which is a DATE:
ALTER TABLE City PARTITION BY RANGE (to_days(citydate))
(
PARTITION p0 VALUES LESS THAN (to_days(‘2007-01-01’)),
PARTITION p1 VALUES LESS THAN (to_days(‘2008-01-01’)),
PARTITION p2 VALUES LESS THAN (to_days(‘2009-01-01’)),
PARTITION p3 VALUES LESS THAN (to_days(‘2009-03-01’)),
PARTITION p4 VALUES LESS THAN (to_days(‘2009-06-01’)),
PARTITION p5 VALUES LESS THAN (MAXVALUE)
);
Note that partitions don’t need to have the same pattern, that is, the same interval. This is very useful—you might want to put older records that are not accessed frequently into one partition and keep recent data in smaller partitions, thus improving performance.
Let’s see “MySQL pruning”* in action. If I run a query that only needs rows from a specific partition, the optimizer will read only the necessary partitions, ignoring the others:
EXPLAIN PARTITIONS SELECT count(*) FROM City WHERE citydate BETWEEN ‘2009-01-01’ AND ‘2009-08-01’;
+—-+————-+——-+————+——+—————+——+———+——+——+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+————+——+—————+——+———+——+——+————-+
| 1 | SIMPLE | City | p3,p4,p5 | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
+—-+————-+——-+————+——+—————+——+———+——+——+————-+
MySQL pruning” is responsible for analyzing the WHERE clause, and if the table is partitioned, it will determine which partitions to read and which to discard for a given query (SELECT).
Note that the optimizer recognized it only needs to use partitions p3, p4, and p5 to find the rows requested by the SELECT above (see the contents of the partitions column).
Now, let me explain why I removed the primary key. There is a rule about using uniqueness constraints (primary keys) with partitioning. The answer is short and straightforward: you cannot have a unique constraint (primary key) on a column that is not used in the partitioning. The reason for this is that when you insert a record, uniqueness (PK) must be checked, and we don’t want to perform a search across every partition to verify uniqueness (PK validation). That would be very costly. Indexes are local to each partition (global indexes are planned for future implementation). Therefore, you can only have a unique constraint if all columns in the constraint (PK) are used in the partitioning expression.
Sarah Sproehnle
MySQL Senior Instructor
Visit our Blog
Learn more about databases
Learn about monitoring with advanced tools
Have questions about our services? Visit our FAQ
Want to see how we’ve helped other companies? Check out what our clients say in these testimonials!
Discover the History of HTI Tecnologia
