MySQL Forums
Forum List  »  General

Best way to simulate a "join" and "limit" during DELETE
Posted by: Michael Botsko
Date: August 07, 2022 10:13AM

Say I have a posts table with a user_id foreign keyed to a users table. I want to delete all posts belonging to user bob but I don't know his user_id upfront. This is happening in an app that users provide their own DB so I don't have control over the server config. To avoid locking tons of rows or tying up the db, I want to delete no more than some limit, say 5000 rows.

I can't DELETE FROM posts USING users LEFT JOIN ... because MySQL doesn't support LIMIT in this case.

It seems like I have these options:

1. Load bob's user_id first and use the delete/limit query without any joins. This seems easiest.

2. Use a subquery DELETE FROM posts WHERE postId IN (SELECT users...) LIMIT 5000

3. Use some form of min/max ID where I SELECT min(postsId), max(postsId) FROM posts and then essentially iterate from min to max in 5000 increments.

I obviously can't know how each would perform but in terms of simplicity, the first option seems best. Easy to write, no joins needed, LIMIT works.

The second seems bad - I've heard bad things about mysql's ability to optimize subqueries like that.

The third can work too but seems like option 1 with extra steps.

I'm looking for any input, advice, or things I hadn't considered. I don't mind writing more code if it means a more efficient query db-side.

Options: ReplyQuote

Written By
Best way to simulate a "join" and "limit" during DELETE
August 07, 2022 10:13AM

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.