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

Reply via email to