How long would it take to test using an index with 18 fields? Might not be crazy.
Gerry On 4/23/10, Nathan Biggs <nbi...@mycfs.com> wrote: > 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 > -- Sent from my mobile device _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
