Partitioning MySQL

This is purely for the sake of partitioning.

In this example, I’ll quickly partition the wordpress comments table into partitions. The hash partition ensures data are evenly distributed across partitions.

mysql> alter table wp_comments partition by hash(comment_ID) partitions 3;
Query OK, 13 rows affected (0.04 sec)
Records: 13  Duplicates: 0  Warnings: 0

Now check that queries are only reaching out to necessary partitions

mysql> explain partitions select * from wp_comments where comment_id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: wp_comments
   partitions: p1
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
        Extra:
1 row in set (0.00 sec)

The hash function can be overridden with any function that returns an integer. For example, I can use mod(some_id,2) to divide some_id into two groups – even and odd.

 264 total views,  2 views today