How many distinct media-types are there? How many distinct facilities do you have? How many rows are typically returned by your FacilityScore subquery?
SELECT facilitynumber,SUM(score_rev) AS score FROM release_cl WHERE media<3 AND year=2006 GROUP BY facilitynumber Regards Tim Romano On Fri, May 28, 2010 at 10:57 AM, Michael Ash <m...@econs.umass.edu> wrote: > 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
