On Fri, Feb 20, 2009 at 05:22:33AM +0000, Kim Boulton scratched on the wall: > Hello, > > I'm trying out Sqlite3 with an eye to improving the performance of > queries on an existing MySQL database. > > I've imported the data into sqlite which is approx. 30 million rows of > part numbers each with a price. > > So far, it's approx. four times slower than the MySQL version, and the > size of the sqlite database is too big to fit in memory (several GB) > whereas I can get the MySQL data down to 900MB if it's compressed and > read only.
Make sure you have indexes on appropriate columns, like part-number or whatever your queries are commonly keyed off of. Bump up the page-cache size. No matter what the size of the database, by default SQLite will only cache 2000 pages. The default page size is 1K, and a cache slot takes about 1.5K, so adjust accordingly (like 10x or 100x) via PRAGMA if you're on a system with a comfortable amount of RAM. If you're randomly pulling out individual records, the only thing you really care about is keeping the most important index(es) in the cache. Also, SQLite will not pre-load anything, so your first few queries are likely to be slower as it seeds the indexes into the data cache. I'm surprised about the size. If you have a name or description column, make sure you're not using fixed-length strings for the import. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
