MySQL Bugs: #60615: partition otimization 2

Bug #60615	partition otimization 2 - partial index
Submitted:	24 Mar 2011 5:52	Modified:	29 Mar 2011 20:07
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S4 (Feature request)
Version:	any	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	partition index, qc

Description:
hi guys, i was thinking about a common problem...

i have some tables with 
(1) hot information partition = write/read intensive, week information
(2) history information partition = 1 write to copy from hot to history, many reads

some index are usefull for hot information partition, 
and some aren't for history information partition

the point is:
could i create a index for field(x) only on partition 1(hot partition)?
with this i will reduce harddisk use for index on partition 2(history partition), since i will never use index on partition 2

something like:

create table xx(
   fields...,
   index hot_index(field_x) >>on partition1<<
) partition by partition_type(fields) (
   partition1 (hot partition options)
   partition2 (history partition options)
)

maybe some others features:

show partitions (some thing like show tables)

repair partition yyy on table xxxx (instead of alter table, just a better interpretation, since today we use... alter table xxx repair partition yyy) this interpretation in nice since we will only lock partition yyy, not the table xxxx (check bug 60190)

How to repeat:
feature request

Suggested fix:
feature request (will take a long time to end, since bug 60190 will need a big change in optimizer)

So, here we have a request for partition-specific indexes.

yes
check that it´s something near to one engine per partition (mix of myisam, innodb, others) in others words, it´s near a optimized 'VIEW'

VIEW CODE:
(SELECT 0 AS partition_field,a.* FROM table_partition_1 AS a)
UNION ALL
(SELECT 1 AS partition_field,b.* FROM table_partition_2 AS b)

fields definitions from a and b are identical but table a have some index, and table b have others. table a can be innodb, table b can be myisam

that´s why (i reported some bugs about it) i think a SHOW PARTITONS, ALTER PARTITON x ON TABLE y, and others DDL are interesting, at table vision (partition engine) we have just field definitions and primary/unique keys (they must be unique for all partitions), it´s something like MERGE engine, but SHOW TABLES can´t show partitions (to avoid user interactiong with it and we lost control about unique keys)

just some doubts...

partition, is a engine? or a myisam/innodb feature?
the code is inside myisam?
maybe could be easy to implement some features if it´s outside myisam/innodb (not fully working, just for a alpha test) and after some improvement and bug fix

it´s near a HANDLER of HANDLERS, like 'raid0-linear' in linux devices but with database words (fields - partition prune)