List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:March 31 2005 8:46am
Subject:Re: Index on boolean column
View as plain text  
> > > >maybe this is a silly question but how useful it is to create indexes
> on
> > >>columns containing only values 0 and 1 (true and false)?
> > >
> > > Perhaps, instead of the index, you might revise your schema a bit.
> > >
> > > Why do you have this boolean column? What are you trying to
> > > achieve?
> > >
> >
> >
> > I use boolean columns as a flags mostly to mark records as 'exported'.
For
> > example I have table 'Transactions' and I have to export data for some
of
> > our customers (never for all of them).
>
> Right. Flags. IMO, flags are bad.
>
> And, as you noticed, it will slow down any queries because a flag cannot
> be indexed properly.
>
> Instead, why create a table
> Transactions_Exported
> TransactionID <primary key>
>
> This can be indexed and will be fast if you want to get info about
> any exported transactions.

PS: if "being exported" is the final stage in the process, you might want
to consider "being not exported" as the triggering condition and store
that instead. That way, you can always quickly find the not-yet exported
transactions.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com

Thread
Index on boolean columnDušan Pavlica30 Mar
  • Re: Index on boolean columnAlec.Cawley30 Mar
  • Re: Index on boolean columnMartijn Tonies30 Mar
  • Re: Index on boolean columnbeacker30 Mar
  • Re: Index on boolean columnDušan Pavlica31 Mar
  • Re: Index on boolean columnMartijn Tonies31 Mar
  • Re: Index on boolean columnMartijn Tonies31 Mar
  • Re: Index on boolean columnDušan Pavlica31 Mar
  • Re: Index on boolean columnMartijn Tonies31 Mar
Re: Index on boolean columnMartijn Tonies31 Mar