Skip navigation links

MySQL Forums :: Newbie :: Partitioning user table


Advanced Search

Partitioning user table
Posted by: Tim Walton ()
Date: September 13, 2009 03:31PM

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.

Options: ReplyQuote


Subject Written By Posted
Partitioning user table Tim Walton 09/13/2009 03:31PM
Re: Partitioning user table Roel Van de Paar 09/14/2009 06:43AM
Re: Partitioning user table Mattias Jonsson 09/14/2009 07:57AM
Re: Partitioning user table Tim Walton 09/14/2009 10:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.