提高子查询速度

Question

这个查询速度很慢，随着表格的增长速度变得越来越慢。有人能找到提高速度的方法吗？

目的是将tblUser和tblLesson的id填充到tblUser_Lesson中，同时确保在插入之前这些id不存在于该表中。

我在tblUser.name和tblLesson.name上建立了索引，但好像没有什么改善。

INSERT INTO tblUser_Lesson (user, lesson)
  SELECT userId, lessonId
  FROM
  (
      SELECT tblUser.id userId, tblLesson.id lessonId
      FROM tblUser,
           tblLesson
      WHERE tblUser.name=?
        AND tblLesson.name=?
  ) tmp
  WHERE NOT EXISTS (SELECT user
                    FROM tblUser_Lesson tmp1
                    WHERE tmp1.user = tmp.userId
                      AND tmp1.lesson = tmp.tblLesson)

- Ninja3412

---

没有JOIN条件？非常意外。 - jarlh

简单的JOIN操作可以大大提高速度... - Veljko89

你想通过这个 INSERT 语句实现什么目的？ - Tom H

你正在从两个表(tbluser、tbllesson)中选择，且没有条件将这两个表关联起来，因此子查询会执行笛卡尔积操作，导致查询结果为n x m条记录。 - Marc B

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - Serg

Answer 1

这是我觉得更易读的查询等效版本：

INSERT INTO tblUser_Lesson(user, lesson)
    SELECT userId, lessonId
    FROM tblUser u CROSS JOIN
         tblLesson l
    WHERE u.name = ? AND l.name = ? AND
          NOT EXISTS (SELECT 1
                      FROM tblUser_Lesson ul
                      WHERE ul.user = u.userId AND ul.lesson = l.tblLesson
                     );

我的第一个建议是让数据库去完成工作。在tblUser_Lesson上创建一个唯一索引：

create unique index unq_tblUser_Lesson on tblUser_Lesson(UserId, Lesson);

然后只需要执行以下插入操作：

INSERT INTO tblUser_Lesson(user, lesson)
    SELECT userId, lessonId
    FROM tblUser u CROSS JOIN
         tblLesson l
    WHERE u.name = ? AND l.name = ? ;

其次，我需要为每张其他表格创建索引：

create index idx_tbluser_name_id on tblUser(name, id);
create index idx_tblLesson_name_id on tblLesson(name, id);

这将加快查询速度。

如果您（通常情况下）不希望在出现重复时出现错误，则可以保留NOT EXISTS子句。 tblUser_Lesson上的索引仍将有所帮助。

- Gordon Linoff

---

很棒的解释，我喜欢它。 - Veljko89

你做得很好 @Gordon。非常棒的回答。我改进了查询语法并提高了插入速度。谢谢！！ - Ninja3412