So to summarize, the only two things key ordering affects (excluding the ability to use partial key matches):
Less unique keys first reduce index size. More unique keys first improve garbage collection in pre 2.0 servers. Thanks for the information. -----Original Message----- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Ann Harrison Sent: Tuesday, October 16, 2012 6:27 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] RE: Order of key elements On Tue, Oct 16, 2012 at 9:47 AM, Rick Debay <rde...@accessrxs.com> wrote: > > neither of these constraints make any sense (the PK itself assures > uniqueness) > See the posts with the title "Unique foreign key for child tables." > It's needed to be the target for a foreign key. > > > UPDATEs used to be slow for non-selective indexes > This is what I'm trying to avoid. Does anyone know it was changed and > what version? We won't finish the migration from FB 1.5 until year end. > > Update is fine for the compound index you described because the full key is guaranteed to be unique. In version 2.0 (I think) the index algorithm was changed so indexes promote the record number to the upper levels, making every key unique. Makes the indexes a bit fatter, but avoids horrible garbage collection behavior. Good luck, Ann [Non-text portions of this message have been removed] ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links
