MySQL Forums
Forum List  »  Newbie

Re: optimising a create table as select
Posted by: Rick James
Date: April 29, 2014 07:10PM

> 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.

Good idea.
Reason 1: Probably less work.
Reason 2: You end up with a more compact table.

There is no "parallel hint". (It possibly would not do any good if it did exist -- the process is I/O bound.)

Unless you have a recent version of MySQL (with Online Alter), adding an index would cost far more than it is worth. Even with online ALTER, it may actually increase your total elapsed time.

If you need to do this form of purging frequently, then seriously consider creating the new table with PARTITION BY RANGE(created_at). That way, subsequent DELETEs can be done using DROP PARTITION, which is virtually instantaneous. (This idea probably won't work because `created_at` is not in the table in question.)

> FROM t1, t2 WHERE t1.id = t2.trip_id

This will _seriously_ impact performance, especially for 10B rows. Please show us
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
and explain whether they are 1:1, 1:many or something else.
SHOW TABLE STATUS LIKE 't1';
SHOW TABLE STATUS LIKE 't2';
SHOW VARIABLES LIKE '%buffer%';

How any rows do you expect in the resulting table?

Options: ReplyQuote




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.