On Tue, Sep 13, 2011 at 8:21 AM, François <francois.goldgewi...@gmail.com>wrote:
> Richard, Igor, > > I made some tests based on a) and b) cases described by Igor. > > I can see much faster SELECT with an index than without it: so Igor > seems to be right. Richard, can you tell me in what cases performance > will be less good? > CREATE TABLE t1(x,y,z BOOLEAN); SELECT * FROM t1 WHERE z; The SELECT above will generally be much faster without an index, unless most values of z are false. > > Thank you, > > Best Regards, > > François > > > On 13 sep, 14:12, François <francois.goldgewi...@gmail.com> wrote: > > Thank you Igor!. > > > > Igor, your a) and b) cases do concern me. But reading your both > > answers I am quite confused now :-) > > > > Best Regards, > > > > François > > > > On 13 sep, 14:03, "Igor Tandetnik" <itandet...@mvps.org> wrote: > > > > > > > > > > > > > > > > > François <francois.goldgewi...@gmail.com> wrote: > > > > Let us consider a table "ITEM" with a BOOLEAN field "FLAG". This > table > > > > may contain up to 100 000 entries and those entries can be selected > > > > using "WHERE ITEM.FLAG = ?" conditions. > > > > > > Is then a good or a bad practice to add an index on this field if we > > > > want to improve SELECT time execution ? > > > > > This may help if and only if a) you have many more records with FLAG=1 > than with FLAG=0 (or vice versa); and b) most of the time, you are looking > up the records belonging to the small subset. For example, if there's a > small number of "active" or recent records that need to be processed, and a > large archive of "processed" records. > > > > > However, in such a case, you might be even better off splitting the > small subset into its own separate table. > > > -- > > > Igor Tandetnik > > > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-us...@sqlite.orghttp:// > sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-us...@sqlite.orghttp:// > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
