MySQL Forums :: Newbie :: optimising a create table as select


Advanced Search

optimising a create table as select
Posted by: Daniel Fisher ()
Date: April 29, 2014 09:29AM

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

Options: ReplyQuote


Subject Written By Posted
optimising a create table as select Daniel Fisher 04/29/2014 09:29AM
Re: optimising a create table as select Peter Brawley 04/29/2014 01:34PM
Re: optimising a create table as select Rick James 04/29/2014 07:10PM
Re: optimising a create table as select Daniel Fisher 05/15/2014 04:16AM
Re: optimising a create table as select Daniel Fisher 05/15/2014 05:02AM
Re: optimising a create table as select Rick James 05/15/2014 11:35PM
Re: optimising a create table as select Daniel Fisher 05/16/2014 05:20AM
Re: optimising a create table as select Rick James 05/16/2014 04:45PM
Re: optimising a create table as select Daniel Fisher 05/19/2014 03:13AM
Re: optimising a create table as select Daniel Fisher 05/19/2014 10:14AM
Re: optimising a create table as select Rick James 05/19/2014 12:02PM
Re: optimising a create table as select Daniel Fisher 05/20/2014 04:06AM
Re: optimising a create table as select Rick James 05/20/2014 05:49PM
Re: optimising a create table as select Daniel Fisher 05/21/2014 08:55AM
Re: optimising a create table as select Rick James 05/21/2014 12:11PM
Re: optimising a create table as select Daniel Fisher 05/22/2014 09:50AM
Re: optimising a create table as select Daniel Fisher 05/23/2014 11:34AM
Re: optimising a create table as select chandra prabhat maurya 05/23/2014 09:04PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.