Okay, here are some steps I've done. 1) normalize db;
I've created single table (item). "computer","process" and "who" fields in log table became rowid integers pointing to this table. "kind" is still a short string. 2) give up covering indexes; For those not aware (http://www.sqlite.org/queryplanner.html , 1.7 Covering Indices) it's an index that has additional columns at it's end ( being selected by query). Covering index eliminate the need to read data from db records. But it increases size of index and size of database. 3) use INDEXED BY to suggest index to query planner. Since indexes are created exactly for specific query. Software was working during weekend. Yesterday's database has 6 mln records and it's only 1.1 Gb in size. Comparing to 4 mln records and 3.5 Gb size before. Now, the long running query took 27 seconds and it has read 50 Mb from database (compare to 2 minutes and 307 Mb before). _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
