Re: optimising a create table as select

[Daniel Fisher]

1. This index is need for this process and is created on the historical_trips table on the created_at column

ALTER TABLE amfam_production.historical_trips
ADD INDEX sk_created_at (created_at);

2. Derive the max ID from the historical_trips table. This historical_trips.id is the foreign key to the historical_readings.trip_id table, this is fairly quick.

Select max(t1.id) as m1
FROM historical_trips t1
Where t1.created_at <= DATE_SUB(sysdate(), INTERVAL 1 MONTH);

3. Derive the max historical_readings.id by using derived trip_id from part 2 and return and record the max_id from the historical_readings table.

Select max(t2.id) from historical_readings t2
Where t2.trip_id = m1

4. Create new table as a clone of the historical_readings table. Believe this preserves all the column types and the auto increment values. This could be a good type to change some of the column types and add in partitioning. In Oracle I can create a reference partition key which use the foreign key id of the parent, not possible in MySQL I don’t think, so will have to Denormalise and add some dates from historical_trips to historical_readings.

Create table historical_readings_new like historical_readings;


5. Populate the new table historical_readings_new with data from historical_readings using an insert into selecting data that is not be archived i.e. it is less than 1 month old. See Steps 2 and 3

Oracle will collapse with an incremental commit i.e. commit every 1000 rows with a (snapshot too old) not sure if MySQL can be optimised with incremental commits

Insert into historical_readings_new select * from historical_readings
Where t2.id > 6847513903;
Commit;

6. Rename table historical_readings and historical_readings_new table ready for the switch.

ALTER TABLE amfam_production. historical_readings RENAME historical_readings_old;
ALTER TABLE amfam_production. historical_readings_new RENAME historical_readings;

Really appreciate your help, hopefully I can repay the favour one day