Re: A first table partitioning exercise.

[patrick flaherty]

thanx Mattias for your help.

To make the posts a little more manageable in terms of reading them, let me address one point at a time.

First

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, PARTITION_EXPRESSION FROM
    -> INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test';
+-------------+----------------+------------+----------------------+
| TABLE_NAME  | PARTITION_NAME | TABLE_ROWS | PARTITION_EXPRESSION |
+-------------+----------------+------------+----------------------+
| animal      | NULL           |          4 | NULL                 |
| no_part_tab | NULL           |    8000000 | NULL                 |
| one         | NULL           |          1 | NULL                 |
| part_tab    | p0             |          0 | year(c3)             |
| part_tab    | p1             |     798458 | year(c3)             |
| part_tab    | p2             |     801766 | year(c3)             |
| part_tab    | p3             |     799590 | year(c3)             |
| part_tab    | p4             |     799553 | year(c3)             |
| part_tab    | p5             |     799538 | year(c3)             |
| part_tab    | p6             |     801742 | year(c3)             |
| part_tab    | p7             |     799579 | year(c3)             |
| part_tab    | p8             |     799586 | year(c3)             |
| part_tab    | p9             |     799557 | year(c3)             |
| part_tab    | p10            |     800631 | year(c3)             |
| part_tab    | p11            |          0 | year(c3)             |
+-------------+----------------+------------+----------------------+
15 rows in set (0.47 sec)

mysql>

OK, that looks pretty much like what you get (why are partitions p0 and p11 empty)?

Another point though (let's add one more since the above was short) is that you populate the non-partitioned table first and then copy the rows into the partitioned table.

The instructions

http://dev.mysql.com/tech-resources/articles/performance-partitioning.html

do it the other way round (1st partitioned 2nd non-partitioned), which didn't quite make sense to me. I should try wiping out the rows in both; filling the non-partitioned first; then the partitioned.