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 <[email protected]>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 <[email protected]> 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: [email protected] > Admin: http://delphi.org.nz/mailman/listinfo/delphi > Unsubscribe: send an email to [email protected] with > Subject: unsubscribe > > > _______________________________________________ > NZ Borland Developers Group - Delphi mailing list > Post: [email protected] > Admin: http://delphi.org.nz/mailman/listinfo/delphi > Unsubscribe: send an email to [email protected] with > Subject: unsubscribe >
_______________________________________________ NZ Borland Developers Group - Delphi mailing list Post: [email protected] Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [email protected] with Subject: unsubscribe
