and Richards patch merely attempts to detect such errors of query phrasing and convert the join type for you ... so that there is no need to generate the possibly millions of unnecessary intermediate results ...
--- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent: Wednesday, 21 March, 2018 12:31 >To: SQLite mailing list >Subject: Re: [sqlite] How to optimise a somewhat-recursive query? > > >Or just try it with the superfluous outer join keyword (LEFT) removed >since you are really just doing an inner (equi) join and the outer >join data is just discarded (by your WHERE clause constraints) after >it is generated anyway ... > >--- >The fact that there's a Highway to Hell but only a Stairway to Heaven >says a lot about anticipated traffic volume. > > >>-----Original Message----- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of Jonathan Moules >>Sent: Wednesday, 21 March, 2018 12:17 >>To: sqlite-users@mailinglists.sqlite.org >>Subject: Re: [sqlite] How to optimise a somewhat-recursive query? >> >>Hi Richard, Simon >>Re: Compiling - I'm afraid I wouldn't really know where to begin. A >>quick google finds >>https://superuser.com/questions/146577/where-do-i-find-nmake-for- >>windows-7-x64 >>- but the answers/links there don't seem to work. I've got to go-out >>now >>but can take another look later and see if I can find a copy >>(Microsoft >>(I'm on Windows) never make it easy to find stuff). >> >>Simon - I suspected the ORDER BY thing was wrong but wanted to check >>first rather than simply come out with "SQLite is broken!". This may >>be >>related to the 3.22 regression I brought up a couple of days ago >(and >>why I'm using 3.15) - probably why Dr H is suggesting I try his >>branch. >>I'm executing the query using SQLiteStudio (Or Python). >>Thanks, >>Jonathan >> >> >>On 2018-03-21 17:58, Richard Hipp wrote: >>> On 3/21/18, Jonathan Moules <jonathan-li...@lightpear.com> wrote: >>>> So, I'm back to being stuck on this. >>>> I have inserted 500,000 random urls (no extra lookups - still >just >>>> 1000), and now the query (as per the below reply) is back to >being >>>> somewhat slow (I'm using sqlite 3.15) at about 0.6s per request. >>> Do you have the ability to compile SQLite from canonical sources? >>If >>> so, please try again with the tip of the join-strength-reduction >>> branch (https://www.sqlite.org/src/timeline?r=join-strength- >>reduction). >>> >>> To compile on Windows: >>> >>> (1) Download a tarball or ZIP archive (or SQLite Archive) and >>unpack it. >>> (2) Run "nmake /f makefile.msc sqlite3.c" >>> >>> On unix: >>> >>> (1) Download and unpack as before >>> (2) ./configure; make sqlite3.c >>> >>> The only dependence for the above is having a "tclsh" somewhere on >>your $PATH. >>> >> >> >>_______________________________________________ >>sqlite-users mailing list >>sqlite-users@mailinglists.sqlite.org >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
