<div dir="ltr">Thanks Edward.<div><br></div><div>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,</div><div><br></div><div>Select everything in the table where IsActive & IsExpense & IsNotYetProcessed & IsTagged</div>
<div><br></div><div>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.</div><div class="gmail_extra">
<div><br>Steve Peacocke<br>Mobile: +64 220 612-611<div><div><a href="http://nz.linkedin.com/pub/steve-peacocke/1/a06/489" target="_blank">Linkedin Professional Profile</a></div></div></div>
<br><br><div class="gmail_quote">On Sun, Mar 30, 2014 at 4:49 PM, Edward Huang <span dir="ltr"><<a href="mailto:edwardh@slingshot.co.nz" target="_blank">edwardh@slingshot.co.nz</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="auto"><div>Hi Steve,</div><div><br></div><div>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. </div>
<div><br></div><div>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. </div>
<div><br></div><div>That's my understanding and experience anyway. </div><div><br></div><div>Cheers,</div><div><br></div><div>Edward Huang<br><br>Sent from my iPhone</div><div><div class="h5"><div><br>On 30/03/2014, at 15:19, Steve Peacocke <<a href="mailto:steve@peacocke.net" target="_blank">steve@peacocke.net</a>> wrote:<br>
<br></div><blockquote type="cite"><div><div dir="ltr">Hi all,<div><br></div><div>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.</div><div>
<br></div><div>These could be as simple as</div>
<div> ActiveRecord (Y/N)</div><div> AccountTransactionType (I/E) - (Income or Expense)</div><div><br></div><div>That last I would normally think would be "Income (Y/N)" so that would be a boolean too.</div><div>
<br></div><div>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.</div><div><br></div><div>
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.</div>
<div><br></div><div>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).</div>
<div><br></div><div>Any thoughts?<br clear="all"><div><br>Steve Peacocke<br>Mobile: <a href="tel:%2B64%20220%20612-611" value="+64220612611" target="_blank">+64 220 612-611</a><div><div><a href="http://nz.linkedin.com/pub/steve-peacocke/1/a06/489" target="_blank">Linkedin Professional Profile</a></div>
</div></div>
</div></div>
</div></blockquote></div></div><blockquote type="cite"><div><span>_______________________________________________</span><br><span>NZ Borland Developers Group - Delphi mailing list</span><br><span>Post: <a href="mailto:delphi@listserver.123.net.nz" target="_blank">delphi@listserver.123.net.nz</a></span><br>
<span>Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a></span><br><span>Unsubscribe: send an email to <a href="mailto:delphi-request@listserver.123.net.nz" target="_blank">delphi-request@listserver.123.net.nz</a> with Subject: unsubscribe</span></div>
</blockquote></div><br>_______________________________________________<br>
NZ Borland Developers Group - Delphi mailing list<br>
Post: <a href="mailto:delphi@listserver.123.net.nz">delphi@listserver.123.net.nz</a><br>
Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
Unsubscribe: send an email to <a href="mailto:delphi-request@listserver.123.net.nz">delphi-request@listserver.123.net.nz</a> with Subject: unsubscribe<br></blockquote></div><br></div></div>