Last active
May 29, 2016 15:08
-
-
Save sameg14/2e426f06dfca471e38644219be2571b8 to your computer and use it in GitHub Desktop.
partition a mysql table by range. In this example we break the table into two partitions, one that contains all category_id < 5 and the other that contains everything else as indicated by maxvalue. Note that this will not work with tables that contain an auto incrementing primary key
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** This table cannot include an auto incrementing numeric primary key **/ | |
alter table users partition by range(category_id)(partition p5 values less than (5), partition pn values less than maxvalue); |
Author
sameg14
commented
May 29, 2016
mysql> select category_id, format(count(*),0) as count from users group by category_id;
+-------------+---------+
| category_id | count |
+-------------+---------+
| 1 | 232,559 |
| 2 | 232,434 |
| 3 | 233,759 |
| 4 | 232,810 |
| 5 | 232,278 |
| 6 | 233,314 |
| 7 | 233,515 |
| 8 | 233,707 |
| 9 | 231,980 |
| 10 | 232,844 |
+-------------+---------+
10 rows in set (1.34 sec)
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment