MySQL Forums
Forum List  »  Newbie

Re: CPU Usage during DELETE and OPTIMIZE
Posted by: Rick James
Date: May 05, 2009 08:14PM

InnoDB or MyISAM?

Use an ID and LIMIT, not OFFSET to do the deletes.

Plan A:
While...
DELETE FROM tbl WHERE ... ORDER BY id LIMIT 1000;
sleep 1
...endwhile

However, that will get stuck once you have 1000 rows that don't match the WHERE.

Plan B:
SELECT @a := min(id) FROM TBL;
While...
SELECT @z := id FROM tbl WHERE ... ORDER BY id LIMIT 1000, 1;
DELETE FROM tbl WHERE ... ORDER BY id BETWEEN @a AND @z;
SELECT @a := @z;
sleep 1
...endwhile

What are you deleting on? Why are you using DELETE + INSERT, not REPLACE? Etc.

Do the OPTIMIZE after the DELETEs and before the INSERTs; the table will be smaller then.

In some situations, ALTER TABLE ... is faster than OPTIMIZE, and possibly better. Suggest a simple ordering:
ALTER TABLE tbl ORDER BY some_field;

If you provide these, I might have more suggestions:
* SHOW CREATE TABLE tbl\G
* SHOW TABLE STATUS LIKE 'tbl'\G
and surround them with [ code ] and [ / code ]

Options: ReplyQuote


Subject
Written By
Posted
Re: CPU Usage during DELETE and OPTIMIZE
May 05, 2009 08:14PM


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.