Konstantin Osipov ([info]kostja_osipov) wrote,
@ 2007-06-25 14:49:00
Previous Entry  Add to memories!  Tell a Friend  Next Entry
On infninite usefulness of DELETE FROM t1 ORDER BY 1
Did you know that MySQL supports:

DELETE FROM t1 ORDER BY a;
?

MySQL manual says:
If the ORDER BY clause is specified, the rows are deleted in the order that is specified.

You can use stored functions and subselects there too if you like.

I should start using this feature immediately.

(5 comments) - (Post a new comment)

useful with LIMIT
[info]jimw
2007-06-25 04:47 pm UTC (link)
i detect a note of sarcasm, but this can be useful in conjunction with a LIMIT clause.

(Reply to this)


[info]jamesd
2007-06-26 09:07 am UTC (link)
You should start using this feature immediately if you're deleting from InnoDB tables:

DELETE FROM t1 WHERE whatever ORDER BY pk

The last customer I used this with reduced their delete time to 60% of its original value. Deleting in the order in which records are clustered in pages is helpful. Jim's right on LIMIT as well - it's good to give InnoDB some low load time for purging deleted records.

(Reply to this)

As Jim said! :)
[info]jeremycole
2007-06-26 08:20 pm UTC (link)
I wrote that feature. :)

As Jim said, it's useful primarily with LIMIT. I hadn't thought about using it with InnoDB, but that makes some sense (although you'd think MySQL could do that itself).

(Reply to this) (Thread)
Re: As Jim said! :)
[info]kostja_osipov
2007-06-26 09:45 pm UTC (link)
Jeremy, no offense, but have you heard about relational algebra? Or this stuff is for nerds? Or what am I missing?

(Reply to this) (Parent)(Thread)
Re: As Jim said! :)
[info]jeremycole
2007-06-26 09:56 pm UTC (link)
Of course I have, but I don't see why it's relevant to this discussion?

Life is all about compromise. :) In any case this was originally a task given by Monty. :)

(Reply to this) (Parent)

(5 comments) - (Post a new comment)

Create an Account
Forgot your login or password?
Login w/ OpenID
English • Español • Deutsch • Русский…