List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:March 31 2005 10:52am
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.
>
> do you think that to join tables 'AllTrans' and 'ExportedTrans' and look
for
> 'ExportedTrans'.'TransID' IS NULL is better and quicker way than using a
> flag column 'Exported'?  Table 'AllTrans' will keep hundreds of thousands
> transactions and approximately half of them will be exported.

To look for non-exported transactions, this might not be the best way :-)

Perhaps a:

Where TransID not in (select TransID from ExportedTrans)

would be better...

> > 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.
>
>
> I have to keep all transactions in one table for our reporting and futher
> analysing and if some other customers need exports I just can create batch
> sripts which will export their data for their own processing.
>
> Thank you for your help and time and also thanks others which responded to
> my question.

The best thing you can do is creating a bunch of test data. Use the flag.
Test it. Next, try other ways. Test it.

Measuring is knowing. And take a look at the query plans involved.

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