How can I do this with MySQL partitions

Question

I have a table with millions of rows and I want to create some partions, but I really don't know how I can to this. I mean I want to have the data which is starting with the ID 1 -> 10000 to be on partition one, and and the data that is starting with the ID 10001 -> 20000 to be on partition two; and so on...?Can you give me an example how to do it?

I have searched a lot on the internet and I read a lot of documentation, but I still don't understand how it needs to be done!

Best Regards,

Answer 1

CREATE TABLE part1 (...)
CREATE TABLE part2 (...)
INSERT INTO part1 SELECT * FROM original WHERE id => 0 AND id < 10000
INSERT INTO part2 SELECT * FROM original WHERE id => 10000 AND id < 20000

Answer 2

This is how you might start using partitions:

ALTER TABLE table_with_millions_of_rows
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (10000),
    PARTITION p1 VALUES LESS THAN (20000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
)

Further information on how to manage (create, delete, change settings etc.) partitions can be found in MySQL manual's "Partitioning" chapter.

Note: partitioning in MySQL is available as of version 5.1.