CODEWITHSUNDEEP
mysqlpartitioning
Scherbius.com
Scherbius.com

Reputation: 3414

Can MySQL table have partitions of a different type?

I have a MySql table that exceeded number of allowed partitions (1024).

I use partitions by value (LIST):

ALTER TABLE phplist_usermessage ADD PARTITION (PARTITION p14 VALUES IN (14))

So the result of:

EXPLAIN PARTITIONS SELECT * FROM phplist_usermessage;

looks like: 

p14,p160,p161,p177,p183,p184,p185,p186,p191,p193,p204, ...... , p1287

Is it possible ( by running ALTER TABLE ... REORGANIZE ) to create 1 partition by RANGE that would hold range of values (for example from 14 to 1000) while keeping partitions by Value p1001,p1002, ... (for values > 1000)?

Can 1 table have a List partition and partition by Range at the same time ?

thanks!!!

Upvotes: 2

Views: 139

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562558

No, you can't have one table be partitioned by both LIST and RANGE.

You can have SUBPARTITIONS but only if the major partitioning is by LIST or RANGE (choose one), and the subpartitions are by HASH or KEY (choose one).

The only way you can achieve something like a mix of LIST and RANGE is if you use the SPIDER storage engine, so that each partition is located on another instance of MySQL, where the table is further partitioned by any means you want. But IMHO the SPIDER storage engine is still experimental and it would impose a lot of overhead for network requests.

Upvotes: 1

Related Questions