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.

• http://dev.mysql.com/doc/refman/5.6/en/partitioning.html
• http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html


> 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.