MySQL UPDATE status from different table

I have a table of articles with a fairly small amount of data (25k rows). The table has a published column (boolean) that needs to be updated depending on the status of data from an external feed.

First I set published = 0 on all articles. Then I load the feed 开发者_StackOverflow中文版data into a temp table with simply the article ID and published = 1. I want to set the published status back to 1 of all the articles that have an ID in the temp table (the temp table only contains published articles-- by default, if it's not in the feed, it needs to be unpublished).

Right now I'm using this:

UPDATE articles a
LEFT JOIN data_temp b
ON a.id = b.id
SET a.published = 1
WHERE b.id IS NOT null

This works, but it's really slow (like 200 seconds slow on my dev server). It seems like there's got to be a way to do this really quickly. Thanks for help.

---

Have you got indexes on the two ID columns of the two tables? If not, this could potentially speed up the join a fair bit.

---

UPDATE articles SET published = 1 WHERE id IN (SELECT id FROM data_temp)

Since it caches the result of the sub-query, it should be pretty fast…