Hi Adam,

> So if my math is right, selectivity equals:
>

<snip>

The selectivity is calculated as :

1 / (Total Number of index nodes / Number of duplicated nodes)

>> And if the number of records changes, indices statistics should be
>> recomputed too, shouldn't it?
>
> For Foreign keys and non unique indices, yes, changing the number of
> records does indeed impact the selectivity.

If the proportion of new records stays the same in a non-unique index compared to the existing one. Then the selectivity
wouldn't change much.

> But what is a primary key?
>
> "A column in a table whose values uniquely identify the rows in the
> table. A primary key value cannot be NULL"
>
> There are no duplicates in a primary key index. If there were, then
> it could not uniquely identify the row and would not be a candidate
> key let alone the primary key.
>
> If the table had 10 records in it

PK Selecticity = 1 / (10 - 0) = 0.1

> If the table had 500 records in it

PK Selectivity = 1 (500 - 0) = 0.002

The lower the selectivity the better it is (compared between indices of the same relation!)

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features:
http://www.firebirdsql.org
http://www.firebirdsql.info

Support list for Interbase and Firebird users:
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep:
news://newsgroups.firebirdsql.info