SQL查询优化 [英] SQL query optimize
本文介绍了SQL查询优化的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在幻灯片中 http://www.slideshare.net/billkarwin/models-for-hierarchical-data,我看到了一个 sql 代码(第 22 页):
In a slide http://www.slideshare.net/billkarwin/models-for-hierarchical-data, i saw a sql code (page 22):
INSERT INTO Comments (author, comment)
VALUES (‘Ollie’, ‘Good job!’);
SELECT path FROM Comments
WHERE comment_id = 7;
UPDATE Comments
SET path = $parent_path || LAST_INSERT_ID() || ‘/’
WHERE comment_id = LAST_INSERT_ID();
我认为我们可以优化它:
I think we can optimize it:
SELECT path FROM Comments
WHERE comment_id = 7;
INSERT INTO Comments (author, comment, path )
VALUES (‘Ollie’, ‘Good job!’, $parent_path || LAST_INSERT_ID() || ‘/’);
我说得对吗?
推荐答案
是的,单个插入比插入后更新更好,但在这种情况下,自动生成的 id(?) 被用作列值也是如此.路径中使用的 LAST_INSERT_ID() 不是当前正在执行的插入语句的 ID,而是最后(上一个)插入的 ID.
Yes, a single insert would be better than an insert followed by an update, but in this case the auto-generated id(?) is being used as a column value as well. The LAST_INSERT_ID() used in the path would not be the id of the current insert statement being performed, but that of the last (previous) insert.
这篇关于SQL查询优化的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文