partition mysql table on a none primary key column

Question

I have a table:

+----+---------+----------+
| id | user_id | comment  |
+----+---------+----------+

Where column type is:

• id (bigint not null primary key autoincrement)
• user_id (bigint not null)
• comment (text)

How can I partition this table on user_id by range? I tried to partition this table by range in PHPMyAdmin but doesn't allow me because user_id isn't a primary key. If I have many 10 billion users and each has an infinite amount of comments this table will be very large. I want to partition it like:

partition 1 (user_id<500)
+----+---------+----------+
| id | user_id | comment  |
+----+---------+----------+
partition 2 (user_id<1000)
+----+---------+----------+
| id | user_id | comment  |
+----+---------+----------+

And so on.

Answer 1

Yes, since user_id is not part of the table primary key(s) or unique keys you can't create partitions solely for the user_id on your table as the DOCs states very clearly

every unique key on the table must use every column in the table's partitioning expression

So for your case what you can do is to add a unique key on your table for both id and user_id

alter table myTable add unique key uk_id_userid (id, user_id);

And then add the range partition for only user_id column as such:

alter table myTable partition by range (user_id) (
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20),
    PARTITION p2 VALUES LESS THAN (30),
    PARTITION p3 VALUES LESS THAN (40)
);

Note Since you already have a table with values in order to define your partition ranges you need to wrap around all existing values for your user_id column in your partitions. That is if you have a user_id of 1000 you can not define your last partition as PARTITION p3 VALUES LESS THAN (1000) that will fail. You will need one more partition i.e.: PARTITION p3 VALUES LESS THAN (2000) or PARTITION p3 VALUES LESS THAN MAXVALUE

See it working here: http://sqlfiddle.com/#!9/8ca7ed

Full working example:

create table myTable (
   id bigint not null auto_increment,
   user_id bigint not null,
   comment text,
   key (id)
) engine=InnoDb;

insert into myTable (user_id, comment) values 
   (1, 'bla'), (1, 'ble'), (1, 'bli'), (1, 'blo'), 
   (12, 'bla'), (12, 'ble'), (12, 'bli'), (12, 'blo'), 
   (23, 'bla'), (23, 'ble'), (23, 'bli'), (23, 'blo'), 
   (34, 'bla'), (34, 'ble'), (34, 'bli'), (34, 'blo');

alter table myTable add unique key uk_id_userid (id, user_id);

alter table myTable partition by range (user_id) (
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20),
    PARTITION p2 VALUES LESS THAN (30),
    PARTITION p3 VALUES LESS THAN (40)
);

Answer 2

Ensure you have satisfied the criteria of when to use partitioning. This is a rather rare case and needs to map closely to your queries. A 500 user range seems tiny. MySQL can handle large tables without partitioning so don't assume its necessary.

The form is:

CREATE TABLE tbl (
id bigint unsigned AUTO_INCREMENT NOT NULL,
user_id bigint unsigned NOT NULL,
COMMENT TEXT NOT NULL,
PRIMARY KEY (user_id, id),
key(id))
PARTITION BY RANGE (user_id) (
  PARTITION p0 VALUES LESS THAN (500),
  PARTITION p1 VALUES LESS THAN (1000),
  PARTITION p2 VALUES LESS THAN (2000),
  PARTITION p3  VALUES LESS THAN (3000)
);

ref: fiddle