Board index » delphi » Database statistics

2005-06-17 06:58:42 AM
delphi177
Hi,
Anybody can help me and explain me how read this index statistic ?
Index RDB$FOREIGN125 (1)
Depth: 3, leaf buckets: 1799, nodes: 1185722
Average data length: 0.00, total dup: 1184098, max dup: 82105
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 75
60 - 79% = 5
80 - 99% = 1718
This is a bad or good index? Why?
Thank in advance.
--
Maxi
 

Maximiliano Robaina writes:
The selectivity is low so using it may not help performance much, at
least in some cases.
More important is that updating the field may be slow (if the old value
was one with a lot of duplicates). Also, deleting such a record will be
slow. All for the same reason (maintaining the indexes).
In such cases I consider adding the PrimaryKey (or some other (near)
unique field) as the last field in the index definition.
Depth=3 is ok. If depth>3 you should consider increasing the page size.
Note: This may have changed with IB/7.x
--
Aage J.
 

What have changed ?
Consider increasing the page size if depth>3 ?
 

Hello, Maximiliano!
Maximiliano Robaina writes:
let's talk about this one here :-)
Index keys are well packed here, so this is very non-unique index.
It holds 1624 different key values, and maximum group size
of some value is 82105 keys.
This index can slowdown restore process, since it have
many duplicates, and this index creation on restore process
can be slow, and, this index also can slowdown garbage collection
for table it is being created on.
Anyway, this is index on foreign key, and you can not create referential constraints
by triggers throwing this FK away (triggers work inside user's transaction only,
while FK sees by index all keys for all concurrent transactions).
If you see or think that this index is "bad", you can drop it only
with conclusion that:
1. applications will never delete records from table that this FK
pointing to.
2. applications will never change primary key field values for table
this FK is pointing to.
If you see that this FK is slowing down some of your queries,
try to use technique to disable this index for optimizer like this:
select * from table
where fk_field+0>5
Optimizer will think that fk_field+0 is not an exact fk_field value, and will not use
index for this "where" condition.
--
Dmitri Kouzmenko, www.ibanalyst.com
 

Maximiliano Robaina writes:
generally means better performance.
Anyhow, I think Ann H. gave you the comprehensive answer on another
list. Note that some of her comments relates to Firebird ("version 2"
means Fb2), and for IB/7 the answers may be different.
--
Aage J.
 

Hi Dmitry,
Thank for you response.
 

Hello!
--
Sincerely yours,
Alexey Kovyazin
www.ibsurgeon.com