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
