List:General Discussion« Previous MessageNext Message »
From:Dušan Pavlica Date:March 31 2005 10:44am
Subject:Re: Index on boolean column
View as plain text  
Martijn,

----- Original Message ----- 
From: "Martijn Tonies" <m.tonies@stripped>
To: <mysql@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
>> 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.

> 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.

Regards,
Dusan 

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