CODEWITHSUNDEEP
mysqldatabasepartitioning
Ricardo Almeida
Ricardo Almeida

Reputation: 55

How can I perform this MySQL partitoning?

I have a table with an integer column ranging from 1 to 32 (this column identify the type of record stored).

The types 5 and 12 represents 70% of the total number of rows, and this number is greater than 1M rows, so it seems to makes sense to partition the table.

Question is: how can I create a set of 3 partitions, one containing the type 5 records, the second containing the type 12 records, and the third one with the remaining records?

Upvotes: 2

Views: 124

Answers (3)

Andrew Kuklewicz
Andrew Kuklewicz

Reputation: 10701

http://dev.mysql.com/doc/refman/5.1/en/partitioning-list.html

create table some_table (
    id INT NOT NULL,
    some_id INT NOT NULL
)
PARTITION BY LIST(some_id) (
    PARTITION fives VALUES IN (5),
    PARTITION twelves VALUES IN (12),
    PARTITION rest VALUES IN (1,2,3,4,6,7,8,9,10,11,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32)
);

Upvotes: 2

Pete Wilson
Pete Wilson

Reputation: 8694

Provided that type is an index, then MySQL has already logically partitioned the table for you. Unless you really need physical partitioning, it seems to me you are only making trouble for yourself.

Upvotes: 0

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26699

Use Partition by list

Upvotes: 0

Related Questions