MySql has a much larger default cache than sqlite3. That could be one rather large difference in performance. Try increasing sqlite3 cache from it's default of 2000k PRAGMA cache_size=100000; Or more... Also...no indexes on media or year? And what does MySql's explain say? Michael D. Black Senior Scientist Northrop Grumman Mission Systems
________________________________ From: sqlite-users-boun...@sqlite.org on behalf of Michael Ash Sent: Thu 5/27/2010 6:07 AM To: sqlite-users@sqlite.org Subject: [sqlite] Aggregate and join query very slow 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
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
