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

partition_table.sql

/** 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);

mysql> describe users;
+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| category_id  | int(10) unsigned | YES  |     | NULL    |       |
| name         | varchar(45)      | YES  |     | NULL    |       |
| date_created | datetime         | YES  |     | NULL    |       |
+--------------+------------------+------+-----+---------+-------+

| users | CREATE TABLE `users` (
  `category_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (category_id)
(PARTITION p5 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION pn VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |

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)