来自不同表的 MySQL 更新状态
我有一个包含相当少量数据（25k 行）的文章表。该表有一个已发布的列（布尔值），需要根据外部源的数据状态进行更新。
首先，我对所有文章设置published = 0。然后，我将提要数据加载到临时表中，其中仅包含文章 ID 和已发布 = 1。我想将已发布状态设置回临时表中具有 ID 的所有文章中的 1（临时表仅包含已发布的文章） -- 默认情况下，如果它不在 feed 中，则需要取消发布）。
现在我正在使用这个：
UPDATE articles a
LEFT JOIN data_temp b
ON a.id = b.id
SET a.published = 1
WHERE b.id IS NOT null
这可行，但它真的很慢（比如在我的开发服务器上慢了 200 秒）。看来必须有一种方法可以非常快速地做到这一点。感谢您的帮助。
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 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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
你有两个表的两个ID列的索引吗？如果没有，这可能会大大加快加入速度。
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.
由于它缓存了子查询的结果，所以它应该很快......
Since it caches the result of the sub-query, it should be pretty fast…