MySQL Bugs: #59769: partition feature/otimization

Bug #59769	partition feature/otimization
Submitted:	27 Jan 2011 4:00	Modified:	27 Jan 2011 13:19
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S5 (Performance)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	partition, qc

Description:
hi guys, could we implement a OTHER partition? for example: bug#40972 could search information on OTHER PARTITION

How to repeat:
for example a ALTER TABLE:
ALTER TABLE any_table
PARTITION by range(any_int_field)(
    PARTITION s0 VALUES LESS THAN (100000),
    PARTITION s1 VALUES LESS THAN (450000),
    PARTITION s2 VALUES LESS THAN (800000),
    PARTITION s3 VALUES LESS THAN MAXVALUE
)

could be rewrite to:

ALTER TABLE any_table
PARTITION by range(any_int_field)(
    PARTITION s0 VALUES LESS THAN (100000),
    PARTITION s1 VALUES LESS THAN (450000),
    PARTITION s2 VALUES LESS THAN (800000),
    PARTITION s3 OTHER VALUES
)

Suggested fix:
every query that have partition prune (maybe) should search OTHER VALUES
NULL values could be insert there
any other type could be done there
it's like:
if not in s0,s1,s2 try s3
if in s0 or s1 or s2 try s3 too

another otimization...

by range (any_bigint_number)

any number in int values could be used in less than
any number bigger or smaller than int range could be put in other values

it's very usefull since any 'wrong' value could be placed in OTHER VALUES
this make list () partition type very easy to implement
for example
create table t(
f enum('a','b')
) parition by list (f)
p1 in ('a')
p2 in ('b')
p3 other values

when we execute:
alter table t change f enum('a','b','c')

we don't need to alter partition information, since 'c' will be found in p3 (other values)

Thank you for the feature request.

we could implement a 
string to int partition function
for example
int = 4bytes
"asbasdfasdf" = "asba" = asc("a") + asc("s")<<8 + asc("b")<<16 + asc("a")<<24
since we can have colation, maybe we could do it without asc function, just internal varible value, the problem is the little or big endian...
maybe just a asc("a") (first character) could be used, maybe a hash string to int could be done
why?
a like operation can be optimized
for example
select * from t where field like 'a%'
partitions with 'a' could be selected
select * from t where field>'a'
partitions without 'a' could be selected
maybe a new function converting from string to int could be very nice...

in future we could make partition not dependent on unique index... doing it we should have a partition check before starting table (engine=partition)

Duplicate of bug#50566. Does not seem like a good idea to have multiple syntax for the same thing (i.e OTHER instead of MAXVALUE for RANGE), makes only sense for LIST partitioning.

Also there are several feature request in this single bug report:
* Partitioning on strings is possible through COLUMNS or KEY partitioning.
* Having unique keys not including the partitioning fields is GLOBAL INDEXES which is not yet implemented.

ncie good work!