Or even better: select fpath from home where not exists (select 1 from work where work.fpath=home.fpath and work.ftype=home.ftype) and ftype = 'f'
with a unique index on home (ftype, fpath) and a unique index on work (ftype, fpath) of course. > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Igor Tandetnik > Sent: Monday, 27 July, 2015 10:04 > To: sqlite-users at mailinglists.sqlite.org > Subject: Re: [sqlite] Query takes 8 seconds on one machine but I abort > after 40+ minutes on other machines > > On 7/27/2015 9:58 AM, Simon Slavin wrote: > > So you're checking both to see that [work.fpath = home.fpath ] and to > see that [work.fpath IS NULL]. This looks weird to me. > > That's a common technique with LEFT JOIN - it's selecting home records > that lack a corresponding work record. In other words, it's equivalent to > > SELECT fpath > FROM home > WHERE fpath NOT IN (SELECT fpath FROM work) > AND home.ftype = 'f?; > > -- > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
