Re: Index on boolean column

[Dušan Pavlica]

Martijn,
----- Original Message ----- 
From: "Martijn Tonies" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
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 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]