Best way to simulate a "join" and "limit" during DELETE

[Michael Botsko]

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.