optimising a create table as select

[Daniel Fisher]

Hi

I looking to delete a whole bunch of data from a table within mysql. I come from an oracle background for many years and if you are deleting more than 50% of the data it is always optimal to recreate the table as opposed to updated or deleting selective rows from a table.

Table t2 in my example has 10 billion rows

create table my_table_new as select t2.*
FROM t1, t2
WHERE t1.id = t2.trip_id
and t1.created_at > DATE_SUB(sysdate(),INTERVAL 2 MONTH);

Is there any way to further optimise this in MySQL. In Oracle I would be inclined to turn off logging and use multiple processes by using the parallel hint.

create table my_table_new as select
SELECT /*+ PARALLEL(t2,4) */ t2.*
FROM t1, t2
WHERE t1.id = t2.trip_id
and t1.created_at > DATE_SUB(sysdate(),INTERVAL 2 MONTH);

Are there any equivalent optimal strategies in MySQL ?

As always thanks.

Daniel