You also need to increase your cache size to match the mysql performance pragma cache 100000; Michael D. Black Senior Scientist Northrop Grumman Mission Systems
________________________________ From: sqlite-users-boun...@sqlite.org on behalf of Michael Ash Sent: Fri 5/28/2010 9:57 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Aggregate and join query very slow Thank you very much. Both replies pointed to indexes. So I changed the indexes and markedly improved performance from 12 seconds to about 1.5 seconds for the faster variant (using nested SELECTS) and about 2.2 second for the slower variant. Per suggestions, I indexed year and media on the big table. So I now have separate indexes for the key variable (releasenumber) and for year and for media. Would it make more sense to have a single index for all three, thus: CREATE UNIQUE INDEX r ON release_cl (year,media,releasenumber); I remain concerned that I am not using indexes as well as possible. The query still takes many times longer than does the same query on MySQL. Thank you very much again for the helpful responses. Best, Michael Ash > 3. Re: Aggregate and join query very slow (Max Vlasov) > Message: 3 > Date: Thu, 27 May 2010 17:26:10 +0400 > From: Max Vlasov <max.vla...@gmail.com> > Subject: Re: [sqlite] Aggregate and join query very slow > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Message-ID: > <aanlktinqmk9csyaguoyllo2okpmsyq3di-tzbuahw...@mail.gmail.com> > Content-Type: text/plain; charset=ISO-8859-1 > > On Thu, May 27, 2010 at 3:07 PM, Michael Ash <m...@econs.umass.edu> wrote: > >> ...These are large tables (52,355 records in facility and 4,085,137 in >> release_cl). >> >> ... >> sqlite> explain query plan >> ...> SELECT name,score >> ...> FROM facility f, (SELECT facilitynumber,SUM(score_rev) AS score >> ...> FROM release_cl >> ...> WHERE media<3 >> ...> AND year=2006 >> ...> GROUP BY facilitynumber) r >> > > > Michael, from what I see, if your release_cl table is not properly indexed > to be quickly aggregated (media and year field), this will lead to full > table reading (so all the data of your 4M records). If it's properly > indexed, and the result number of records of this select is big, consider > adding non-indexed fields to this (or brand-new) index since otherwise > sqlite quickly finds records with this index, but have to look up main data > tree to retrieve other fields. > > Max, > maxerist.net _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
