MySQL Forums :: Newbie :: Partitioning user table

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.