On Fri, Sep 21, 2012 at 9:43 PM, Rick James <rja...@yahoo-inc.com> 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.ada...@gmail.com]
> > Sent: Friday, September 21, 2012 5:09 AM
> > To: mysql@lists.mysql.com
> > 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.ada...@gmail.com>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
> > >
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
