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.

There is no benefit from partitioning the table. Use the indexes, but not the partitioning, that danblack suggests. — Rick James, Jul 26 '20 at 05:51
thanks, Rick james.but as I mentioned I have for example 10billion users, and each user can have multiple comments.let's say every user posts 1 million comments.that means in this table I have 1 million rows for one user.and every partition will be 1000000*500.500 — Daniyal Madmolil, Jul 26 '20 at 18:29
Let's see the important `SELECTs`; I'll explain why non-partitioning is just as good as partitioning, perhaps even better. I'll start with: If all `SELECTs` include `where user_id = constant`, then having `user_id` first in the relevant index is very good; even better is to have `PRIMARY KEY(user_id, ...)` — Rick James, Jul 26 '20 at 19:49

score 1 · Answer 1 · answered Jul 26 '20 at 01:51

1

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

answered Jul 26 '20 at 01:51

danblack

12,130
2
22
41

You must make the auto_increment column first in the PK in InnoDB. – Bill Karwin Jul 26 '20 at 01:54
I [tried](https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=ff0f21f0b1de766b1de9683bd2e0fe4e) on all version from MySQL-5.5+ and all available mariadb versions. In all cases the AI seemed to be incrementing. Why must it be first? – danblack Jul 26 '20 at 02:06
1

Ah, I see. It works because `id` is the first column of another index. – Bill Karwin Jul 26 '20 at 02:36
1

yep. and for completeness `key(id)` couldn't be declared unique because of the partitioning. – danblack Jul 26 '20 at 02:38
The only requirement for an auto_inc column is that it be the _first_ column in _some_ index. Neither `UNIQUEness`, not `PRIMARYness` is required. – Rick James Jul 26 '20 at 05:45
1

@danblack - The partitioning buys no performance benefit. The trick with `PRIMARY KEY(user_id, id)` does. But that can be used exactly as written with a non-partitioned table. – Rick James Jul 26 '20 at 05:48
thanks.but as I mentioned I have for example 10billion users, and each user can have multiple comments.let's say every user posts 1 million comments.that means in this table I have 1 million row for one user.and every partition will be 1000000*500.500 users and each user 1 million comments.if I do not partition table it will be very large as time past.may be after one month I have 10 billion rows in that table.are indexes are god for any large table like more than 10 billion and infinity table that grows as time passes. – Daniyal Madmolil Jul 26 '20 at 18:30
fantasy numbers aside, big doesn't mean needing partitions. Partitions are good for dropping and swapping. But sure, index it correctly. – danblack Jul 26 '20 at 22:38
[examine the nodejs result object](https://www.tutorialkart.com/nodejs/nodejs-mysql-result-object/), it dffers depending on the sql statement type. – danblack Jul 27 '20 at 01:30

score 0 · Accepted Answer · answered Jul 26 '20 at 01:54

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

partition mysql table on a none primary key column

2 Answers2