I create indexes and my original query time dropped from 16 seconds to this:
real 0m5.928s user 0m5.361s sys 0m0.565s However, when I use the query you suggested, it was a little slower: real 0m9.827s user 0m8.952s sys 0m0.873s On Mon, Jul 27, 2015 at 5:38 PM, Keith Medcalf <kmedcalf at dessus.com> wrote: > > 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 > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
