Bug #29120 new partition type
Submitted: 14 Jun 2007 18:31 Modified: 5 Apr 2011 6:03
Reporter: Roberto Spadim (Basic Quality Contributor) Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: Partitions Severity:S4 (Feature request)
Version:any OS:Any
Assigned to: CPU Architecture:Any
Tags: qc
Triage: Triaged: D5 (Feature request)

[14 Jun 2007 18:31] Roberto Spadim
Description:
continue Bug #27727
i was thinking... today mysql partitions are "index" partitions
for example
we can't do:
create table a(b,c,primary(b))
and partition table by field "c"
this problem could be allowed using primary(b,c)
but the primary key could be bigger and bigger...
what i'm thinking... partition could be a optimizer option
for example partitioning using "c" field could allow us to check where each value of "c" is without an "index", for example
partitioning c:
c>0 and c<10 -> partition 1
c>10 and c<20 -> partition 2
other case -> partition 3
and now i ask
where is c=40?
i can say without index that it could be at partition 3, if it exists

very good, today i don't found a way to make this without "declaring" "c" as an index, i know that it could be an problem if we change at filesystem partition 1 and 3, but it could't be done by DBA, we could check at startup or another strategy

How to repeat:
create an table with 2 fields, the first is primary key
partition table using ther second using this algorithm
field>0 and field<30 -> partition1
field>40 and field<50 -> partition2
other cases -> partition3

Suggested fix:
an problem that we could get:
field>0 and field<30 ->partition1
field>20 and field<40 -> partition2
others -> partition3

if we get field=21 it could be at partition 1 or 2

some options at optmizer could be created to workaround this situation
[15 Jun 2007 7:22] Valeriy Kravchuk
Thank you for a reasonable feature request.
[18 Aug 2009 22:01] Mattias Jonsson
I don't see how this will ensure the uniqueness of the primary key / unique key, and if a row can go to partition 1 OR 2 how could that be deterministic?

Could you please give a more detailed explanation over how this new type of partitioning should work?
[18 Sep 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[29 Mar 2011 18:56] Roberto Spadim
hi guys, i was reading again:

it´s not deterministic i see it now it´s a problem since we must read from partition 1 and 2 (not a optimized feature)
the nice part is OTHERS VALUES 
could it be implemented? i don´t know if it´s a duplicated of others bugs since this one is very old (2007)
[5 Apr 2011 6:03] Roberto Spadim
in this case unique/primary keys must be at table level (not at partition level)
rows and others indexes could/should be at partitions