List:General Discussion« Previous MessageNext Message »
From:Rick James Date:September 21 2012 4:13pm
Subject:RE: Partitioning on a Substring of Varchar Column in Mysql
View as plain text  
Some statements are incorrect...

If you PARTITION on (`x`), then `x` must be included in every UNIQUE key.  That includes
the PRIMARY KEY.

You do not need to PARTITION on the PK.

An AUTO_INCREMENT value, say `id` must be the _first_ field in _some_ key.  `id` does not
have to be UNIQUE, nor does it have to be the PK.  (However, if you do not have
"UNIQUE(`id`)", by itself, it is possible to _explicitly_ create duplicate ids.  This is
not likely in normal practice.)

So, if you are partitioning on dt_dtamp, and you have id AUTO_INCREMENT, then PRIMARY KEY
( 'id','dt_dtamp') is sufficient.  UNIQUE ('id') is unnecessary (and disallowed).

If `id` is a string, then you _could_ (in 5.5.x?) PARTITION BY RANGE(id) and specify
VALUES LESS THAN...

WHY do you want to use PARTITIONing?  I ask because I find a lot of people think they want
to PARTITION, yet they have not determined that there will be any benefit.

See tips in
http://mysql.rjweb.org/doc.php/ricksrots


> -----Original Message-----
> From: Adarsh Sharma [mailto:eddy.adarsh@stripped]
> Sent: Friday, September 21, 2012 5:09 AM
> To: mysql@stripped
> Subject: Re: Partitioning on a Substring of Varchar Column in Mysql
> 
> Just update the string needed function substring(id,9,6).
> 
> On Fri, Sep 21, 2012 at 5:18 PM, Adarsh Sharma
> <eddy.adarsh@stripped>wrote:
> 
> > Hi all,
> >
> > I created a partition on a timstamp column ( dt_dtamp ) in
> > mysql-5.1.58 testing table.But as all of us know that partitioned
> > column need to be primary key.
> > I have already id column as primary key but my application cann't
> > afford  primary key ('id','dt_stamp') in a table.
> >
> > I thought & tried PRIMARY KEY ( 'id','dt_dtamp')  & UNIQUE ('id')
> will
> > work but below error came :
> >
> > ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the
> > table's partitioning function
> >
> > as it is clearly mentioned in
> > http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-
> partit
> > ioning-keys-unique-keys.html
> > .
> >
> > Is it possible i can create partitioning on a substring of
> > varchar(255) ( id ) column. Below are the some contents of my id
> > column :-
> >
> >  0038000-120614070130414-sudoie-sudoi-C@86 |
> > | 0038000-120614070130414-sudoie-sudoi-C@87 |
> > | 0038000-120614070130414-sudoie-sudoi-C@88 |
> > | 0038000-120614070130414-sudoie-sudoi-C@89 |
> > | 0038000-120614070130414-sudoie-sudoi-C@90 |
> > | 0038000-120614070130414-sudoie-sudoi-C@91 |
> > | 0038000-120614070130414-sudoie-sudoi-C@92 |
> > | 0038000-120614070130414-sudoie-sudoi-C@93 |
> > | 0038000-120614070130414-sudoie-sudoi-C@94 |
> > | 0038000-120614070130414-sudoie-sudoi-C@95 |
> > | 0038000-120614070130414-sudoie-sudoi-C@96 |
> >
> > It includes date in it. Can i make my partitioning on string after
> > first hyphen : 120614
> >
> > Is it possible in mysql5.1.58 or may be higher versions so that i
> have
> > only primary key ('id') on my table. Need a crack for it.
> >
> >
> > Thanks
> >
Thread
Partitioning on a Substring of Varchar Column in MysqlAdarsh Sharma21 Sep
  • Re: Partitioning on a Substring of Varchar Column in MysqlAdarsh Sharma21 Sep
    • RE: Partitioning on a Substring of Varchar Column in MysqlRick James21 Sep
      • Re: Partitioning on a Substring of Varchar Column in MysqlAdarsh Sharma23 Sep