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 <rdebay@...>
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