CODEWITHSUNDEEP
mysql
Allover
Allover

Reputation: 127

MySQL Delete Performance

I'm deleting rows using a cron tab set to run every hour. For performance and less fragmentation, what is the best way to do this?

Also, should I run optimize table after the delete has finished?

Upvotes: 1

Views: 1376

Answers (1)

Nick Clark
Nick Clark

Reputation: 4467

The answer will depend on your data and how many rows you're deleting at a time.

If possible, delete the rows with a single query (rather than one query per row). For example:

DELETE FROM my_table WHERE status="rejected"

If possible, use an indexed column in your WHERE clause. This will help it select the rows that need to be deleted without doing a full table scan.

If you want to delete all the data, use TRUNCATE TABLE.

If deleting the data with a single query is causing performance problems, you could try limiting how many rows it deletes (by adding a LIMIT clause) and running the delete process more frequently. This would spread the deletes out over time.

Per the documentation, OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns).

Optimizing the table can be very expensive. If you can, try deleting your data and optimizing the table once per day (at night). This will limit any impact to your users.

Upvotes: 1

Related Questions