Increase speed of subquery

This query runs slowly, and gets slower as the table grows. Can anyone find a way to increase the speed?

It is intended to populate tblUser_Lesson with id's of tblUser and tblLesson while ensuring that the id's don't exist in the table before insert.

I have an index on tblUser.name and tblLesson.name, but it doesn't seem to make a difference.

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)

Solution

This is an equivalent version of the query that I find easier to read:

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
                     );

My first recommendation is to let the database do the work. Create a unique index on tblUser_Lesson:

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

Then just do the insert as:

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

Second, I would create indexes for each of the other tables:

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

That speeds this query.

If you (in general) don't want to get an error when there is a duplicate, then you can leave your NOT EXISTS clause in place. The index on tblUser_Lesson will still help.