> I think you want the most specific column first in the index

That was my understanding. But if Sean has been doing it the other way,
then the impact is probably minimal.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Mark Rotteveel
Sent: Monday, October 15, 2012 2:01 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] RE: Order of key elements

On 15-10-2012 18:14, Leyne, Sean wrote:
> Rick,
>
>> What is the effective difference between these two key elements
>> orderings?
>>
>> ALTER TABLE T ADD CONSTRAINT U UNIQUE (PK, HIGH_MAX_DUP)
>>
>> ALTER TABLE T ADD CONSTRAINT U UNIQUE (HIGH_MAX_DUP, PK)
>>
>> The first version is how I normally order key elements, from most
>> unique to least. Since a primary key index exists, the second would
>> allow HIGH_MAX_DUP to be used in queries. But would ordering the key

>> elements like this have any impact due to the high number of
>> duplicates (it's almost as bad as an even split between Boolean
values).
>
> Personally, I always work from generic to most specific.
>
> I have always had the impression that it produces the most compact
> indexes since the engine will use "prefix compression" to group index
> items together. (I am trying to explain the general approach -- the
> description is not technically correct)

That doesn't sound right to me, but I am sure that Ann will jump in and
educate me (us). I think you want the most specific column first in the
index, as you want to narrow down the search-space quickly. Using the
least specific column first is (almost) equivalent to using a low
selective index.

Mark
--
Mark Rotteveel


------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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