Not quite. The older version brought it down to 8 seconds (as compared to 5 seconds) but still a whole lot better than 40+ minutes.
It is very interesting to see an index can make such a remarkable difference in that particular scenario. This is my first time creating indexes but I will definitely be doing that more in the future. >From what I read, it was recommended to create indexes for any field used in the SELECT statement. Is that correct? Give my original query: SELECT home.fpath FROM home LEFT JOIN work ON work.fpath = home.fpath WHERE work.fpath IS NULL AND home.ftype = 'f'; What indexes should I create? Seems like I only need an index on home.fpath and work.fpath but I wanted to make sure. On Tue, Jul 28, 2015 at 3:13 PM, Simon Slavin <slavins at bigfraud.org> wrote: > > On 28 Jul 2015, at 7:55pm, rotaiv <rotaiv at gmail.com> wrote: > > > That is the machine. Originally. it was taking 40+ minutes. I upgraded > to > > the latest version and it decreased to 16 seconds. With indexes, 5 > > seconds. :-D > > Ah, okay I get it. In that case the index would probably have given you a > time of 5 seconds even without the update. Creating good indexes is key to > fast searching times in SQL. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
