----- Original Message -----
From: "Martijn Tonies" <m.tonies@stripped>
Sent: Thursday, March 31, 2005 10:46 AM
Subject: Re: Index on boolean column
>> > > >maybe this is a silly question but how useful it is to create
>> > > >indexes
>> > >>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'.
>> > example I have table 'Transactions' and I have to export data for some
>> > 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
>> 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.
> 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
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