Hi, Given the following two tables:
TABLE tracks ... composer REFERENCES artists(id) conductor REFERENCES artists(id) performer REFERENCES artists(id) TABLE artists id name I'm trying efficiently to find a list of tracks given the name of an artist (used in composer or conductor or performer). I had come up with the following 2 solutions: 1) SELECT * FROM tracks WHERE (SELECT id FROM artists WHERE name LIKE 'John Williams') IN (performer,conductor,composer); query plan: 0|0|0|SCAN TABLE tracks 0|0|0|EXECUTE LIST SUBQUERY 1 0|0|0|EXECUTE SCALAR SUBQUERY 1 1|0|0|SCAN TABLE artists 2) WITH artist_match(id) AS (SELECT id FROM artists WHERE name LIKE 'John Williams') SELECT * FROM tracks WHERE (composer IN artist_match OR conductor IN artist_match OR performer IN artist_match); query plan: 0|0|0|SCAN TABLE tracks 0|0|0|EXECUTE LIST SUBQUERY 1 1|0|0|SCAN TABLE artists 0|0|0|EXECUTE LIST SUBQUERY 2 2|0|0|SCAN TABLE artists 0|0|0|EXECUTE LIST SUBQUERY 3 3|0|0|SCAN TABLE artists Both seem to run pretty quickly. But I'm wondering whether there's anything I can improve here. Timing these queries, it seems that 2) is a bit faster (which I can see why). But It still seems to use 3 separate subqueries? What does the EXECUTE LIST and EXECUTE SCALAR mean? Biggest question, is there a way to do this more efficiently? Thanks, Sander _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
