I'm having trouble finding a way to partition a specific table.
I have a table that stores 20 million users:
user_id | int(10) PRIMARY
username | VARCHAR(25) UNIQUE
(other columns removed for example's sake)
I'd like to be able to partition this table by the username so I can do faster lookups to retrieve the user_id.
At first I tried partitioning with range and this failed:
ALTER TABLE users PARTITION BY RANGE( username ) (
PARTITION p0 VALUES LESS THAN ('m'),
PARTITION p1 VALUES LESS THAN(MAXVALUE)
);
# VALUES value must be of same type as partition function near [ the 'm' part]
It seems that I can't use strings in partition values?
So I tried converting the username to a number:
ALTER TABLE users PARTITION BY RANGE( ORD(username) ) (
PARTITION p0 VALUES LESS THAN (109),
PARTITION p1 VALUES LESS THAN(MAXVALUE)
);
# This partition function is not allowed
So it doesn't support ORD.
The other issue is that I don't think I can use KEY or HASH partitioning because my user_id column is the primary key and I don't want to use that for the partition.
ALTER TABLE users PARTITION BY KEY ( username ) PARTITIONS 10
#1503 - A PRIMARY KEY must include all columns in the table's partitioning function
This seems like something that should be doable, any thoughts?
Edited 1 time(s). Last edit at 09/13/2009 03:38PM by Tim Walton.