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
