This is really great information. Are there any rules on when an index
is more trouble to maintain than it is worth? Say if I have an index
with a selectivity of 0.333 over 44 records. Also, it seems like one
could easily be misled, for example, you might have a completely unique
index but only currently have two records, thus resulting in a
selectivity of 0.5.

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Tuesday, November 04, 2003 8:57 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Selectivity Question


At 10:22 PM 4/11/2003 -0500, you wrote:

>Hello all,
>
>Are there any hard rules about selectivity for indexes and the numbers
>that I should look for? I in the process of writing an app that will
>house many record in a FB 1.5 DB and I want to make sure that I have

not

>introduced indices that will adversely affect performance.

1. Run SET STATISTICS on the database containing real-life data.
2. Do a query to get the current selectivity for your indexes:

SELECT RDB$INDEX_NAME, RDB$STATISTICS
FROM RDB$INDICES
WHERE RDB$RELATION_NAME NOT STARTING WITH 'RDB$'

Here's what Ann says regarding this magic number in RDB$STATISTICS:
It is the selectivity. It's set when index fast-load runs (e.g. restore

from a backup, reactivate) and as a result of the
SET STATISTICS INDEX <name>;
command. The formula is simple.
-- If the index is empty, then the value is 0.
-- Otherwise, the value is
1 divided by (the number of index entries - the number of
duplicates).

The smaller the selectivity value, the better the index.

I'd add, the larger the selectivity value, the LOWER the selectivity of
the
index. A unique index has the lowest selectivity value possible and it
has
the highest possible selectivity.

However, you should also run
gstat -index db_path_and_name -t table-name
to look at the figures on the index trees on each index. Look at the
figures for nodes (= number of entries in the index), total dup (the
number
of nodes that have duplicates) and max dup (the length of the longest
duplicate chain). These will tell you useful stuff about the geometry
of
the index. Long duplicate chains are a sign of an index you should
drop.

h.



Yahoo! Groups Sponsor
ADVERTISEMENT




To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.