From: Adarsh Sharma Date: September 23 2012 4:59am Subject: Re: Partitioning on a Substring of Varchar Column in Mysql List-Archive: http://lists.mysql.com/mysql/228229 Message-Id: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary=20cf3063e24feda66f04ca5755bd --20cf3063e24feda66f04ca5755bd Content-Type: multipart/alternative; boundary=20cf3063e24feda66904ca5755bb --20cf3063e24feda66904ca5755bb Content-Type: text/plain; charset=ISO-8859-1 On Fri, Sep 21, 2012 at 9:43 PM, Rick James wrote: > 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. > Yes true, but my application requires primary key on ( `id` varchar(255) NOT NULL, ) column. I cannot alter the schema. So if i need to partition on created column then i need to put primary key as ( id,created_time ) that is not accepted because this causes duplicate records of id under different created times. i.e i ask in my first can we partition on substring from id column. > > 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). > I don't have auto increment column in my table , my primary key is ( `id` varchar(255) NOT NULL, ) > > If `id` is a string, then you _could_ (in 5.5.x?) PARTITION BY RANGE(id) > and specify VALUES LESS THAN... > Is the below function works in 5.5 PARTITION BY RANGE ( substring(id,9,6) ) ( -> PARTITION c0 VALUES LESS THAN ( substring('0000014-120412124227546-o-C@1469',9,6) ), -> PARTITION c1 VALUES LESS THAN ( substring('0000014-120512124227546-o@1469',9,6) ), Rt now not supported in 5.1.5 > 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. > > I need partitioning because three tables in my database are populated in seconds , right now size > 40 GB. We don't need data older than 15 days , right now we are deleting ( purging ) older records manually that causes fragmentation and a lot of manual effort.PFA schema of a table. So I decided to partiton on created_time column & purge records by deleting partitions but I cannot change the primary key (id) to primary key ( id,created_time) column i.e the only concern i have. All is working fine as the proper indexes guided by you but I cannot use partitioning to solve this issue if i need to change primary key i.e i m moving to partition on substring of id column if feasible. Any other way i can solve this issue. Thanks for your link also , it is very helpfull. Thanks > 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 > > 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 > > > > --20cf3063e24feda66904ca5755bb Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Fri, Sep 21, 2012 at 9:43 PM, Rick Ja= mes <rjames@stripped> wrote:
Some statements are incorrect...

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

You do not need to PARTITION on the PK.


Yes true, but my application requires primary key on ( `id`= varchar(255) NOT NULL, ) column. I cannot alter the schema.
So if i need to partition on created column then =A0i need to put primary k= ey as ( id,created_time ) that is not accepted because this causes duplicat= e records of id under different created times.

i.e= i ask in my first can we partition on substring from id column.
=A0

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

So, if you are partitioning on dt_dtamp, and you have id AUTO_INCREMENT, th= en PRIMARY KEY ( 'id','dt_dtamp') is sufficient. =A0UNIQUE = ('id') is unnecessary (and disallowed).

I don't have auto increment column in my table , my primary = key is =A0(=A0=A0`id` varchar(255) NOT NULL, )

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

Is the be= low function works in 5.5=A0

=A0PARTITION BY = RANGE ( substring(id,9,6) ) (
=A0 =A0 -> =A0 =A0 PARTITION c0 VALUES LESS THAN ( substring('0= 000014-120412124227546-o-C@1469',9,6) ),
=A0 =A0 -> =A0 = =A0 PARTITION c1 VALUES LESS THAN ( substring('0000014-120512124227546-= o@1469',9,6) ),

Rt now not supported in 5.1.5
=A0
=
WHY do you want to use PARTITIONing? =A0I ask because I find a lot of peopl= e think they want to PARTITION, yet they have not determined that there wil= l be any benefit.

I need partitioning because three tables in my databa= se are populated in seconds , right now size > 40 GB. We don't need = data older than =A015 days , right now we are deleting ( purging ) older re= cords manually that causes fragmentation and a lot of manual effort.PFA sch= ema of a table. So I decided to partiton on created_time column & purge= records by deleting partitions but I cannot change the primary key (id) to= primary key ( id,created_time) column i.e the only concern i have.

All is working fine as the proper indexes guided by you= but I cannot use partitioning to solve this issue if i need to change prim= ary key i.e i m moving to partition on substring of id column if feasible.<= /div>

Any other way i can solve this issue. Thanks for your l= ink also , it is very helpfull.

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


> -----Original Message-----
> From: Adarsh Sharma [mailto:e= ddy.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&#= 39;t
> > afford =A0primary key ('id','dt_stamp') in a tabl= e.
> >
> > I thought & tried PRIMARY KEY ( 'id','dt_dtamp= 9;) =A0& UNIQUE ('id')
> will
> > work but below error came :
> >
> > ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in th= e
> > table's partitioning function
> >
> > as it is clearly mentioned in
> > http://dev.mysql.com/doc/refman/5.1/en/partit= ioning-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<= br> > > column :-
> >
> > =A00038000-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 afte= r
> > 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
> >

--20cf3063e24feda66904ca5755bb-- --20cf3063e24feda66f04ca5755bd--