Hi Steve
The topic you refer to is index selectivity. If you have a table with an index involving one field and it has only two values the index is not as useful in searches as is, say a primary key fields which has a unique value for each row. Of course "useful" depends on your needs. <Select * from MyTable where myBool ='F' > will return half the table. See http://www.firebirdfaq.org/faq167/ "index selectivity" This poor performance is not a FB feature, it is a common DB feature (I think). To get a well performing index, make it with thought for what you want the index to help you with. HTH Russell From: Steve Peacocke [mailto:[email protected]] Sent: Sunday, 30 March 2014 8:32 p.m. To: [email protected]; NZ Borland Developers Group - Delphi List Subject: Re: [DUG] Using Boolean (Char(1)) in Firebird Thanks Russell, Yes, I created a domain .. CREATE DOMAIN BOOL AS Smallint DEFAULT 0 NOT NULL ; And while it is possible to place an index on it, it's my understanding that Firebird will not perform well when placing an index on this type of field where there is limited ranges (e.g. M/F; 1/0; Y/N). It was my understanding that Firebird may not even create this type of index. Perhaps I am wrong?? 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 7:31 PM, russell <[email protected]> wrote: Hi Steve Firebird Fb does not have a native boolean type. I use a smallint for my Boolean types. 1=True, 0=F. There are many ways to define and use Booleans in Fb. Eg. CREATE TABLE BCD_CONNECTIONS ( CONNECTION_ID ID /*Integer NOT NULL*/ NOT NULL, FIRST_TS TIMESTAMP NOT NULL, CURRENT_TS TIMESTAMP NOT NULL, MACHINE TEXT24 /*Varchar(24) */ DEFAULT 'Unknown' NOT NULL, SECURITY_CODE TEXT48 /*Varchar(48) */ DEFAULT 'illegal' NOT NULL, MACHINE_ID Integer, USER_ID Integer, USER_NAME TEXT64 /*Varchar(64) */, B_IN_WINSLEEP Smallint DEFAULT 0, CONSTRAINT BCD_CONNECTIONS_PK PRIMARY KEY (CONNECTION_ID) ); So these Booleans can be indexed. Tables can have many Booleans. ID, TEXTNN are my defined Domains in Fb. Russell From: [email protected] [mailto:[email protected]] On Behalf Of Steve Peacocke Sent: Sunday, 30 March 2014 3:19 p.m. To: NZ Borland Developers Group - Delphi List Subject: [DUG] Using Boolean (Char(1)) in Firebird 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 <tel:%2B64%20220%20612-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
