<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>
myOffice Email Message
</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1">
<meta name="date" content="2002-11-01">
<style type="text/css">
</style>
</head>
<body>
<span style=
"color:#FF0000 "><b><span style=
"font-family:MS Sans Serif ">[Reply]</span></b></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">Hi Steve and others,</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">Here's a selection of comments from my database of Interbase & Firebird community comments.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">From what I can ascertain, there is no hard and fast rule here, and you really need to test your data against different configurations - with indexes, and without - and see what works best.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">cheers</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">Gary</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">====================================</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">http://www.firebirdfaq.org/faq166/</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">If you have a table with a lot of records and one of the fields stores values like 'Y' and 'N' or something similar (i.e. not a lot of different values) it does not help performance to create index on such field. Even worse, if you do create index and it does get used it will slow down the queries. Such index has low selectivity (number of records one index node identifies is huge - perfect selectivity is when one index node identifies one record). </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">You might want to consider adding that field to some other (primary key for example) which is already indexed and often used in WHERE clause or JOINs. This is very effective with Firebird 1.x, while Firebird 2.x and higher are smarter and work around those duplicates. </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">========================================================================</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">>Should I use Integer/Big Int & concatenate my Boolean fields, or should I</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">>convert them to char(1) values of "y"/"n" etc or smallint values of 0/1.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">Use char(1). The engine will pick a fast comparison since it knows its</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">comparing one byte to another. If it makes you happy, make them charset</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">octets and store 0x1 and 0x0.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">Regards,</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">Ann harrison</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">========================================================================</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">> Can someone please tell me if I should build an index on a field when </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">> the the values for the field is always a boolean value (Yes or No).</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">> I execute a lot of queries against this table based on whether the field </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">> is 'No'. Everyday approximately 1000 new rows of data gets stored into </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">> this table where the field will be 'No'. The table has millions of </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">> records present where the value has been set to 'Yes', and they cannot </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">> be removed.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">Index on columns like this is incredibly dense, so it's often very</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">shallow (which is a good thing) and it's significantly smaller (also</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">good thing). This means that its processing overhead on selects is lower</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">than on average index. However, it has higher than average overhead for</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">maintenance, especially when keys are updated or deleted (problem is not</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">in update itself, but in subsequent garbage collection). In your case</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">when data are mostly permanent, the maintenance overhead could be</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">neglected. In your case, when there is a significant imbalance in key</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">distribution, you would get significant performance improvement for</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">queries that filter rows with less common key value, and you may suffer</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">performance hit for queries for complementary queries (at least until</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">data distribution statistics would be implemented in Firebird). However,</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">these indices have very poor general selectivity value, so Firebird</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">optimizer may decide to not use the index even if it would improve</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">performance, so you always need to check the query plan to verify</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">whether such index isn't useless.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">To sum it up, there is no general rule that would recommend or not to</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">use such indices. The actual usefulness of such index depends on type of</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">queries you mostly use, and physical data storage characteristics that</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">affect optimizer decisions. The best approach to evaluate its usefulness</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">is to try it with your real data and queries (and don't forget to check</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">the execution plans).</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">best regards</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">Pavel Cisar</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">IBPhoenix</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">========================================================================</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">When you create a index, it add's time cost to updates and inserts. So if you don't need the index, droping then will improve inserts and updates.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">To know more about index in Boolean you can take a look on the REPLYies of this message:</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">http://tech.groups.yahoo.com/group/firebird-support/message/106608</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">also you can google using this below (without the quotes)</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">"site:http://tech.groups.yahoo.com/group/firebird-support index on Boolean"</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">Theres a post of Ann H. that explain it wonderfully ^_^ (i just can't find it now o_o'' )</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">regards,</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">???</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#000000 ">A</span><span style=
"color:#FF0000 "><b>t 10:29 on 31/03/2014 you wrote </b></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Hi Steve</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>I read your original email again and note:</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>My understanding is that this will never be indexed, even if you</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>specifically add an index to it. So how do you handle it. There may be</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>several boolean fields in a table definition.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>As these tables can contain several hundred thousand records, this could</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>potentially slow down any query to say total all records last 3 years where</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Active and Income - as the only index would then be on the date field, there</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>is a possibility that this could potentially be a very slow query.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>I've heard of others creating another table to create, say, non-Avtive</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>record ID's, but this one table could have several booleans, therefore</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>creating several new tables (combining then into a single table with the</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>field name would cause the same problem).</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>I am trying to interpret for myself what your concern is. Indexing and using</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Booleans in tables, resulting in slow queries?</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>You have a general concern without a specific example. You understanding</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>about something "never indexed" is not clear to me. Maybe you have in mind</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>that an optimiser will not use some indices because an index has a Boolean</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>in it? </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>One way to respond to your concern is to discuss index selectivity and</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>optimisers. I won't do this as I don't know enough about the FB query</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>optimiser. So I'll respond pragmatically. I am using Booleans in many tables</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>and in many queries in software with biggest table 600,000 records and</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>400MB+ DB file sizes. In FB 2.5.x. To the best of my knowledge I do not have</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>slow query problems with Booleans.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>If you have slow queries there are options to look at. We'd need to get</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>specific. I would not let a concern for using Booleans worry me. </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>I hope I have understood you in a reasonable sense.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Kind regards,</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Russell</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>From: delphi-bounces@listserver.123.net.nz</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>[mailto:delphi-bounces@listserver.123.net.nz] On Behalf Of Steve Peacocke</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Sent: Monday, 31 March 2014 9:15 a.m.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>To: NZ Borland Developers Group - Delphi List</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Subject: Re: [DUG] Using Boolean (Char(1)) in Firebird</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Thanks Jolyon,</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Silly me, I thought this was going to be simple.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Add Index, don't add index, it's a Boolean decision, I never expected NULL</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>as an answer :)</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Steve Peacocke</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Mobile: +64 220 612-611</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Linkedin Professional Profile</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">><http://nz.linkedin.com/pub/steve-peacocke/1/a06/489> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>On Mon, Mar 31, 2014 at 8:56 AM, Jolyon Smith <jsmith@deltics.co.nz> wrote:</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>I don't think you can adopt a general rule for all boolean type conditions</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>in data. In the two example fields you cite, for example, I can see that</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>there is a potential difference in the nature of the booleans involved.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>ActiveRecord - looks like something that could change over time. A record</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>that was active may become inactive and I further speculate that there will</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>over time be far more inactive records than active ones.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>AccountTransactionType - looks like something that is fixed. The type of a</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>transaction seems unlikely to change once that transaction has been</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>recorded. You might call this a "static" boolean, as opposed to the more</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>"dynamic" nature of the previous example.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Of course, more specific domain knowledge may reveal these assumptions to be</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>invalid, but you get the general idea.... the characteristics of a</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>particular datum go beyond it's simple data type and those characteristics</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>in turn determine the most appropriate implementation (which in turn will</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>depend on whether the dominant context is OLTP or OLAP - i.e. efficiency of</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>creating/modifying data vs efficiency of queries).</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>In the case of "static" booleans for example, you might consider creating</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>separate tables for records of different values in this field. For</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>convenience of querying all records you can of course project a view which</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>unions the two (or more) tables involved, with a derived, virtual column</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>containing the discriminating field value. This also opens up the</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>possibility that the most efficient indexes for rows of a certain type (i.e.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>now table) may well be different than those for the other. i.e. the way you</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>work with Income transactions might benefit from different indexes than</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Expense transactions.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>On the other hand, the way you work with income and expense transactions may</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>mean that you are better off having indexes operating over ALL transactions,</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>regardless of Income/Expense type.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>See what I mean about "the best way" being dependent on far more than just</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>the data type ?</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>And there's still more to it than that...</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>w.r.t index selectivity, I am not convinced that the 1 / # of distinct</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>values metric is a particularly reliable measure. It surely assumes an even</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>distribution of distinct values across the data set ?</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>i.e. if you have 100,000 records and they have a column where 50,000 rows</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>have one value and 50,000 have another, then yes, the efficiency and thus</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>the utility of any index on that value is going to be negligible (but then,</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>no better than having no index isn't actually *worse*, is it ? Although</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>there will be some overhead introduced in maintaining the index, though I</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>doubt this will itself be hugely significant).</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>On the other hand, if only 1,000 of those 100,000 records have one value and</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>the remaining 99,000 have another, AND if your application most often</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>queries that table to select those in the smaller subset (the 1,000) then</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>whilst an index may not be of any benefit for querying the 99,000, it surely</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>will provide benefit for those queries that select the 1,000 (or from among</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>them), a benefit which *might* be worth the overhead of maintaining that</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>index even though it provides little/no benefit for the handful/minority of</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>queries that work with the 99,000 records ?</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>The bottom line is, there is no shortcut for properly understanding your</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>data and the way your application(s) work(s) with that data for correctly</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>tuning your database structure and metadata for optimal performance.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>:)</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>On 30 March 2014 15:19, Steve Peacocke <steve@peacocke.net> wrote:</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Hi all,</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>I'm playing around with a Firebird database and wanted to know from you DB</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>experts out there how you handle booleans in a table.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>These could be as simple as</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> ActiveRecord (Y/N)</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> AccountTransactionType (I/E) - (Income or Expense)</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>That last I would normally think would be "Income (Y/N)" so that would be a</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>boolean too.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>My understanding is that this will never be indexed, even if you</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>specifically add an index to it. So how do you handle it. There may be</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>several boolean fields in a table definition.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>As these tables c an contain several hundred thousand records, this could</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>potentially slow down any query to say total all records last 3 years where</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Active and Income - as the only index would then be on the date field, there</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>is a possibility that this could potentially be a very slow query.</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>I've heard of others creating another table to create, say, non-Avtive</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>record ID's, but this one table could have several booleans, therefore</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>creating several new tables (combining then into a single table with the</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>field name would cause the same problem).</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Any thoughts?</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Steve Peacocke</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Mobile: +64 220 612-611 <tel:%2B64%20220%20612-611> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Linkedin Professional Profile</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">><http://nz.linkedin.com/pub/steve-peacocke/1/a06/489> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>_______________________________________________</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>NZ Borland Developers Group - Delphi mailing list</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Post: delphi@listserver.123.net.nz</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Admin: http://delphi.org.nz/mailman/listinfo/delphi</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Unsubscribe: send an email to delphi-request@listserver.123.net.nz with</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Subject: unsubscribe</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>_______________________________________________</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>NZ Borland Developers Group - Delphi mailing list</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Post: delphi@listserver.123.net.nz</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Admin: http://delphi.org.nz/mailman/listinfo/delphi</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Unsubscribe: send an email to delphi-request@listserver.123.net.nz with</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Subject: unsubscribe</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">> </span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">></span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>_______________________________________________</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>NZ Borland Developers Group - Delphi mailing list</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Post: delphi@listserver.123.net.nz</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Admin: http://delphi.org.nz/mailman/listinfo/delphi</span></span>
<p>
<span style=
"font-family:MS Sans Serif "><span style=
"color:#008000 ">>Unsubscribe: send an email to delphi-request@listserver.123.net.nz with Subject: unsubscribe</span></span><p>
<font face=arial size = 1 color = Navy><font color=gray face = "helvetica,verdana,arial" size = 1><br>
<font size=2 color="black">Gary Benner </font>MNZCS ITCP<br>
Information Technology Certified Professional <br>
<a HREF="http://www.onlearn.co.nz" style="text-decoration:none; color:blue">Onlearn Limited</a> - Online Learning Hosting & Support, Training & Content Development<br>
<a HREF="http://www.123.net.nz" style="text-decoration:none; color:blue">123 Internet Limited</a> - Managed Web Hosting, Virtualisation, High Availability Systems & Cluster Technologies<br>
<a HREF="http://www.semantic.co.nz" style="text-decoration:none; color:teal">Semantic Limited</a> - Software Development & Systems Design, Online Education, e-Commerce<br>
<a HREF="http://www.diwa.co.nz" style="text-decoration:none; color:teal">Disaster Warning Systems Limited</a> - Public Emergency Warning and Communication Systems<br>
<b>Mob:</b> 021 966 992<br>
<b>DDI:</b> +64 7 543 1206<br>
<b>Email:</b> <a href="mailto:gary@benner.co.nz" >gary@benner.co.nz</a><br>
<b>Skype:</b> garybenner<br>
</font><br>
<br>
Ref#: 41006<br>
<br>
</body>
</html>