copying from unpartitioned to partitioned table
Posted by: Rick Casey
Date: March 09, 2010 11:54AM
I have a real world problem to solve that greatly help my project.
I have a large MySQL database (450MM recs) up at AWS, where the records are in one large, unindexed, unpartitioned table. Of course, this is unusable: queries simply don't work against a unindexd table of this size.
My challenge is to get this data into a partitioned table. I have successfully created a partitioned table with this command:
CREATE TABLE new2 (
PARTITION BY RANGE(phonenumber) (
PARTITION p0 VALUES LESS THAN (1000000000),
PARTITION p1 VALUES LESS THAN (2000000000),
PARTITION p2 VALUES LESS THAN (3000000000),
PARTITION p3 VALUES LESS THAN (4000000000),
PARTITION p4 VALUES LESS THAN (5000000000),
PARTITION p5 VALUES LESS THAN (6000000000),
PARTITION p6 VALUES LESS THAN (7000000000),
PARTITION p7 VALUES LESS THAN (8000000000),
PARTITION p8 VALUES LESS THAN (9000000000),
PARTITION p9 VALUES LESS THAN MAXVALUE
I attempt to copy the data from the unpartitioned table into the partitioned table with this command:
INSERT INTO new2 (
phonenum,callstarted,status,attempts,duration,CAST(phonenum AS UNSIGNED)
When this query finishes running (which takes a while, an hour?), the total number of records is correct in both tables, but most of the data in the partitioned table is NULL; there appears to be just a few scattered records in the first few inserts that contain any data at all.
Note that during the copying (insert) query, I have to cast the original phonenum from a varchar to an int. I do this because I believe that partitioning cannot be done on a varchar. My new2 table has the same structure as the unpartitioned table (allcalls3), except that I have added phonenumber int onto the end.
Can anyone point out what I'm doing wrong here? Is there any other technique to get this data from the unpartitioned into the partitioned table?
Any suggestions or insights would be MUCH appreciated!