At 10:00 AM 28/11/2003 +1300, you wrote:

>Hi,
>
> >> A couple of questions...
> >> What engine version # are you using?
>
>Have been testing it on 1.5 RC 6
>
> >> What is the selectivity of the BigTable.Status index?
>By selectivity, do you the number of different items or the index
>statistics? The status value has 9 values, most of them would be either
>8 or 9. Only the 'current' ones are less than 8. The index information
>is as follows:
>
> Statistics: 0.100000001490116119
>
> Index BigTable_IDX2 (6)
> Depth: 3, leaf buckets: 2406, nodes: 3264049
> Average data length: 0.00, total dup: 3264039, max dup: 3166674
> Fill distribution:
> 0 - 19% = 0
> 20 - 39% = 1
> 40 - 59% = 0
> 60 - 79% = 0
> 80 - 99% = 2405
>
>Not sure what all that means, or where to find out what that all
>means...

It means that, of 3264049 nodes (rows where the index value isn't null) you
have
3166674 all of one single value and only about 97,000 of other
values. That is a single chain of more than 3 million. That screams "drop
me!! with a compelling shrillness. If it's a FK, drop the constraint.

The "statistics" value indicates about 1 in 8 or 9, which says that you
have this number of values represented across the data. It's helpful at
design time to alert you to a potential selectivity problem, and the
optimizer uses it to guess whether the index would be useful if 9 values
were evenly distributed across the data, it would be OK. However, it's a
two-edged sword, since it won't indicate anything about the length of the
chains in the actual data.

If you think you need to index this column, then create a composite index
consisting of (this column and the PK).

Helen