On Nov 4, 2009, at 5:41 PM, Levent Serinol wrote: > > I wrote a new application four our company which will be used to > replace our maildir (file) based mail system into sqlite db files > for every user. Everything looks perfect except select queries on > big tables is slow. > > > Briefly my schema is like this, > > mail_id auto inc primary key, uid varchar, from varchar,reply_addr > varchar, mail_header blob, whole_mail blog, subject varchar, folder > int, mail_flags int,....
mail_id needs to be INTEGER PRIMARY KEY for maximum efficiency. The AUTOINCREMENT part is ok to add but probably does not matter in your application. See http://www.sqlite.org/autoinc.html > > I have created indexes for uid,from,flags,subject,mail_flags which > is mandatory on a mail application. I have imported 28,000 emails in > a sqlite database and 118,000 emalis into another database file. > they are completely different database files for test purposes. The > problem is when I do select query as below: > > select COUNT(mail_id) from mails_table where folder=2 and > mail_flags=1; CREATE INDEX some_index_name ON mails_table(folder,mail_flags); > > it takes a huge amount of time to calculate the results. > first database file is 600 MB for 28,000 mails. > second database file 2.7GB for 110,000 mails. > > I run the sqlite3 command line utility with strace to figure out > what is going on, What I saw is, sqlite is reading whole pages > (every column for every row). The query is not using for example > subject and mail_header fields. But strace shows that it's been read > by sqlite itself while walking the table. > > What do you suggest for the solution ? > separaring the blogs into another table and let small columns on > main table ? Separating the mail body BLOBs into a separate table would help too. But the index above will help the most, I suspect. D. Richard Hipp d...@hwaci.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
