[DUG] Using Boolean (Char(1)) in Firebird

[Steve Peacocke]

Thanks Edward.

I could try it but I can't see where a potentially milt-char query without
an index of some sort could help. for (a non-sql) example,

Select everything in the table where IsActive & IsExpense &
IsNotYetProcessed & IsTagged

To me that would simply scream through every single one of the potentially
millions of records in the table and do a multiple-compare only to return
perhaps a hundred records.

Steve Peacocke
Mobile: +64 220 612-611
Linkedin Professional
Profile<http://nz.linkedin.com/pub/steve-peacocke/1/a06/489>


On Sun, Mar 30, 2014 at 4:49 PM, Edward Huang <edwardh at slingshot.co.nz>wrote:

> Hi Steve,
>
> Since a Boolean field condition only cuts result record count by roughly
> half, it's generally less efficient to have an index on it. Database
> scanning speed is generally fast enough that referring to an index of such
> actually will slow down query speed.
>
> But if you have a combination of a number of Boolean field and specific
> values that will be queried often, and only small portion of records with
> that combination, then it's possibly beneficial to have an index on that
> specific combination.
>
> That's my understanding and experience anyway.
>
> Cheers,
>
> Edward Huang
>
> Sent from my iPhone
>
> On 30/03/2014, at 15:19, Steve Peacocke <steve at peacocke.net> wrote:
>
> Hi all,
>
> I'm playing around with a Firebird database and wanted to know from you DB
> experts out there how you handle booleans in a table.
>
> These could be as simple as
>   ActiveRecord (Y/N)
>   AccountTransactionType (I/E) - (Income or Expense)
>
> That last I would normally think would be "Income (Y/N)" so that would be
> a boolean too.
>
> My understanding is that this will never be indexed, even if you
> specifically add an index to it. So how do you handle it. There may be
> several boolean fields in a table definition.
>
> As these tables c an contain several hundred thousand records, this could
> potentially slow down any query to say total all records last 3 years where
> Active and Income - as the only index would then be on the date field,
> there is a possibility that this could potentially be a very slow query.
>
> I've heard of others creating another table to create, say, non-Avtive
> record ID's, but this one table could have several booleans, therefore
> creating several new tables (combining then into a single table with the
> field name would cause the same problem).
>
> Any thoughts?
>
> Steve Peacocke
> Mobile: +64 220 612-611
> Linkedin Professional Profile<http://nz.linkedin.com/pub/steve-peacocke/1/a06/489>
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at listserver.123.net.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at listserver.123.net.nz with
> Subject: unsubscribe
>
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at listserver.123.net.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at listserver.123.net.nz with
> Subject: unsubscribe
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20140330/52b3fc87/attachment.html