On Thu, May 27, 2010 at 3:07 PM, Michael Ash <[email protected]> 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 [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
