CODEWITHSUNDEEP
performancesqlite
user1094081
user1094081

Reputation:

SQLite query, subquery and performance improvement

In SQLite is there any other way to write this statement:

SELECT tape_id,title 
FROM tapes 
WHERE tape_id NOT IN (SELECT tape_id 
                      FROM tapeGenres)

and improve performances?

Upvotes: 0

Views: 1448

Answers (2)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

Another option might be to use NOT EXISTS but the biggest performance improvement would be gained by using appropriate indexes.

If not already present, you might try adding

  • an index on tapeGenres.tape_id
  • a covering index on tapes (tape_id, title)

SQL Statement

SELECT tapes.tape_id, tapes.title
FROM   tapes t
WHERE  NOT EXISTS (SELECT tape_id FROM tapeGenres WHERE tape_id = t.tape_id)

Upvotes: 1

T I
T I

Reputation: 9933

Consider using a LEFT JOIN

SELECT tapes.tape_id, tapes.title
FROM tapes
LEFT JOIN tapeGenres ON tapes.tape_id = tapeGenres.tape_id;
WHERE tapeGenres.tape_id IS NULL

Upvotes: 1

Related Questions