Wednesday, May 15, 2013

MySQL count and select from a partition

The MySQL Forums had a question about how to count rows per partition.
I figured it would make a good blog post as well.

First these are some good links to have for partition review and at least get you started. The examples used here reference examples started with these pages.

> CREATE TABLE t2
    -> (
    ->   dt DATE
    -> )
    -> PARTITION BY RANGE (TO_DAYS(dt))
    -> (
    ->   PARTITION p01 VALUES LESS THAN (TO_DAYS('2007-01-01')),
    ->   PARTITION p02 VALUES LESS THAN (TO_DAYS('2008-01-01')),
    ->   PARTITION p03 VALUES LESS THAN (TO_DAYS('2009-01-01')),
    ->   PARTITION p04 VALUES LESS THAN (MAXVALUE));

> desc t2;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| dt    | date | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+

INSERT INTO t2 VALUES ('2007-02-02'),('2008-02-02'),('2009-02-02'),(CURDATE());
Query OK, 4 rows affected (0.04 sec)


OK so now we can select from the partition as well as count from them..

> select count(*) from t2;
+----------+
| count(*) |
+----------+
|        4 |
+----------+

> explain partitions select count(*) from t2 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: p01,p02,p03,p04
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: NULL

> select * from t2 PARTITION (p02);
+------------+
| dt         |
+------------+
| 2007-02-02 |
+------------+


> select * from t2 PARTITION (p04);
+------------+
| dt         |
+------------+
| 2009-02-02 |
| 2013-05-15 |
+------------+

> select * from t2 PARTITION (p02,p04);
+------------+
| dt         |
+------------+
| 2007-02-02 |
| 2009-02-02 |
| 2013-05-15 |
+------------+

> select count(*) from t2 PARTITION (p04);
+----------+
| count(*) |
+----------+
|        2 |
+----------+


Hope this helps.