I am new to sqlite3, converting from mysql. A query that involves an aggregate function and a join is running very slowly (taking about 15 seconds compared to mysql where it runs in <1 second). I've tried two variants of the query (each reprinted below with the explain query plan), and both are roughly equally slow. These are large tables (52,355 records in facility and 4,085,137 in release_cl).
Please let me know if you have advice; I am clearly not getting it. 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 ...> WHERE f.facilitynumber=r.facilitynumber ...> ORDER BY score DESC ...> LIMIT 10 ; 0|0|TABLE release_cl WITH INDEX facilitynumberRC ORDER BY 0|1|TABLE AS r 1|0|TABLE facility AS f WITH INDEX FacilityNumberF sqlite> explain query plan ...> SELECT name,city,state,SUM(score_rev) AS score ...> FROM release_cl r,facility f ...> WHERE r.facilitynumber=f.facilitynumber ...> AND media<3 ...> AND year=2006 ...> GROUP BY r.facilitynumber ...> ORDER BY score DESC ...> LIMIT 10 ; 0|0|TABLE release_cl AS r WITH INDEX facilitynumberRC ORDER BY 1|1|TABLE facility AS f WITH INDEX FacilityNumberF -- Michael Ash, Associate Professor of Economics and Public Policy Department of Economics and CPPA University of Massachusetts Amherst, MA 01003 Email m...@econs.umass.edu Fax +1-413-545-2921 http://people.umass.edu/maash _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
