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

<?php

$startTime = strtotime('now');

$mysqli = new mysqli("app.restream.vm", "root", "root", "test");

for($i=0; $i < 1000000; $i++){
        $mysqli->query('insert into users(category_id, name, date_created) values("'.rand(1,10).'", "'.md5(strtotime('now').rand(1,100000)).'", NOW())');
}

$endTime = strtotime('now');

$diff = $endTime - $startTime;

echo PHP_EOL. 'Script took: ' . ($diff / 60) . ' minutes to execute' . PHP_EOL;

root@app:/var/lib/mysql/test# ls -hal
total 189M
drwx------ 2 mysql mysql 4.0K May 29 05:43 .
drwx------ 6 mysql mysql 4.0K May 29 05:20 ..
-rw-rw---- 1 mysql mysql 65 May 29 03:55 db.opt
-rw-rw---- 1 mysql mysql 8.5K May 29 03:55 tnp.frm
-rw-rw---- 1 mysql mysql 8.5K May 29 05:43 users.frm
-rw-rw---- 1 mysql mysql 28 May 29 05:43 users.par
-rw-rw---- 1 mysql mysql 76M May 29 05:43 users#P#p5.ibd
-rw-rw---- 1 mysql mysql 112M May 29 05:43 users#P#pn.ibd

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)