Menu
blog.headdesk.me
blog.headdesk.me

Partitioning MySQL

Posted on 2014/03/052014/03/05

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.

facebookShare on Facebook
TwitterTweet

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Full text search

Recent Posts

  • Dumping AWS Organization tree
  • Free is the most expensive
  • Terraform conditional resource and blocks
  • Upgrade Ubuntu 16.04 to latest release
  • Inspect and control network traffic on AWS
  • aws (8)
  • coffee (1)
  • headfi (1)
  • linux (7)
  • others (55)
  • security (2)
  • tech (36)
  • wordpress (2)

apache aws awscli azure backup cloud coffee coreos distributed filesystem docker ec2 EL8 elasticcache etckeeper featured heartbleed kernel linux mail meltdown mysql php pine python rdp rds Redhat Red Hat RHEL RHEL7 rpm Ryzen snapshot spectre SSL systemd tech terraform ubuntu ubuntu upgrade vector vpn wordpress xtreemfs yum

©2022 blog.headdesk.me | Powered by SuperbThemes & WordPress