Most selective columns first (was: Re: Key Compression vs. Selectivity)

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Mon, 18 Dec 2006 09:31:35 +0100
Message-ID: <4un1v0F18ol4sU1@mid.individual.net>

On 18.12.2006 07:35, hasta_l3_at_hotmail.com wrote:
> Jonathan Lewis a écrit :

>> "Robert Klemme" <shortcutter_at_googlemail.com> wrote in message
>>> The general rule of thumb for indexing is to put the most selective
>>> columns first in order to foster fast lookups.  (Of course there are
>>> other considerations involved, which single index covers most queries
>>> etc.)
>> The "most selective first" has always been a little
>> flexible (at least since 6.0 appeared)

>
>
> Jonathan and Robert....
>
> Why would the rule of thumb "put the most selective column first"
> be a good one to any degree ?
>
> (let's assume that permutations of index columns don't
> impact the number of queries covered)

Because - that's at least the theory - more selective columns cut down the number of rows faster than less selective columns. Assume you have a people table with gender and year of birth. Gender has low selectivity, so if you query by gender="male" and year="1984" with the gender column first in the index, the first index seek step just cuts the number of records (and thus key entries) in half (roughly) while selection on year will select a much smaller portion of the data (assuming not every person in that table was born in 1984).

But, there are of course other considerations - that's why I just called it a rule of thumb.

Regards

        robert Received on Mon Dec 18 2006 - 02:31:35 CST