-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 stephen liu wrote: > For addressbook application, it call select_all_address_for_one_user > frequency. > If multipile users' addresses store in one table, the > select_all_address_for_one_user > will cause much I/O operations.
If you have an index on the user column then the index will be used to control getting entries from the table of all entries. Admittedly there will still be some seeking since the various user entries will be interleaved over time, but it will be the minimum amount necessary. Without an index, SQLite will have to look at every row. There are fairly frequent postings to this list where people detail their schemas and query patterns and knowledgeable people help with query optimization. A second approach is to just have one table per user (eg addressbook_user) with some appropriate scheme for munging the username. It also sounds like you are operating in an environment that is almost entirely reading and with lots of memory. You could still store one SQLite disk file per user but combine them in memory for fast access (and not having to worry about I/O latency). [To do this, create your table in :memory:, attach each user file, insert into memorytable select .... from userdb.table, detach user file] Roger -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkn3/LMACgkQmOOfHg372QQOHACgiO3NvIzrLkyBABV+NasJuxWA AXEAoKSZnMrHv+ze2hhDo10AUqdugM/S =fYN3 -----END PGP SIGNATURE----- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
