If insert speed is important are you doing batch inserts? If so, you want to do a BEGIN/COMMIT to speed up your inserts a LOT. Default action is to defer which mean no database locks occur during your inserts. http://www.sqlite.org/lang_transaction.html Michael D. Black Senior Scientist Northrop Grumman Mission Systems
________________________________ From: sqlite-users-boun...@sqlite.org on behalf of Nathan Biggs Sent: Fri 4/23/2010 7:50 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Direct access of table data Max, thanks for the information. That will be very useful for other table queries, but not for this one. For my table in questions there are 18 fields. I think an index with 18 fields would be a little crazy. On 4/23/2010 3:06 AM, Max Vlasov wrote: > > > ...As I add more restrictions on the where-clause it > > tends to slow down. I realize that this is due to my indexes, but can't > > add a lot of indexes because it slows down the insert speed which is > > more important than the query speed. > > > > > Nathan, maybe you already knew but just in case... > > if your select relies on an index and also queries fields not presented in > that index, consider appending these extra fields to the index. It doesn't > make sense in terms of search speed, (moreover it will increas the db > size), > but this will save time since no extra lookup will be taking place. > > So if you have table > CREATE TABLE a, b, c, > > and index > CREATE INDEX ON a, b > > and use query similar to > SELECT a, b, c ... WHERE a= and b = > > sqlite will do extra lookup to get c from the table, > > but if you change the index to > > CREATE INDEX ON a, b, c > the same query will get all the data from the index itself saving time and > the amount of data flow. > > I did a quick test and it showed not only a noticable difference in time, > but also a significant difference in amount of the data read. > > Max, > maxerist.net > _______________________________________________ > 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
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
