Saturday, September 23, 2006

State of MySQL partitioning

It's been some time since I last blogged. I've been quite busy erasing all the
bugs from the partitioning implementation in MySQL 5.1. At the moment
there is 1 bug left in review and a few on its way into the main clone. The
rest of the bugs are fixed and already in the 5.1 clone. So the next 5.1
release (5.1.12) will have partitioning ready for tough tests. So if you have
been waiting for partitioning to stabilise it's time to try it out now with your
application and see how it works.

I watched an interesting animation my daughter made about how partition
pruning using dynamic PowerPoint slides. Really interesting to see what can
be done if one knows how to handle these types of tools. She's quickly
becoming our family authority on presentations.

Lately we've also started working on some extensions for the partitioning
hopefully ready for 5.2. We have introduced a new partitioning syntax like this:

CREATE TABLE t1 (a char(10), b date)
PARTITION BY RANGE (COLUMNS(b,a))
(PARTITION p0 VALUES LESS THAN ('1999-01-01', "abc"),
PARTITION p1 VALUES LESS THAN ('2001-01-01', MINVALUE),
PARTITION p2 VALUES LESS THAN ('2003-01-01', MAXVALUE));

The nice thing with this syntax is that it can be handled partition pruning with
ranges in a very efficient manner. So the query:
SELECT * FROM t1 WHERE b <= '1999-06-01' AND b >= '1999-02-01';
can be optimised to only scan partition p1.

We are also working on indexes that are partitioned independent of the base
table and also a couple of other features. As usual what actually goes into the
next release is uncertain.
at

5 comments:

  1. Anonymous10:05 PM

    Can we see those presentations ?

    ReplyDelete
  2. Mikael Ronstrom11:10 PM

    I'll look into what I can do to make them
    available in some manner or the other.
    I'll post it on the blog when it happens

    Mikael

    ReplyDelete
  3. Yotke12:14 PM

    Hello

    Is there a way to assign data to partition automatically? E.g., in case of to_days(timestamp); do I have to set all options -->
    ...PARTITION p0 VALUES ...
    PARTITION p1 VALUES...
    PARTITION p2 VALUES...
    In my case, I would like the partition number to be set automatically according to the to_days output.

    ReplyDelete
  4. Yotke12:23 PM

    Given table that consists of store_id(unique-index), partition by range (to_days(timestamp))...

    It is written that partition must be added to the unique-index since the unique-index is local to a partition.
    If I am good that the unique constraint will be validated locally per partition, can I leave store_id(unique-index) without the column (timestamp) used for the partitioning?

    ReplyDelete
  5. Mikael Ronstrom1:35 PM

    Hi Yotke,
    It isn't possible to automatically create new partitions.

    If you partition on timestamp, then this needs to be part of all unique indexes.

    ReplyDelete