On Thu, May 5, 2011 at 6:39 PM, Randall Sell <randallsell@...> wrote:

> Next Q...
>
> Create View MyView(.....)
>  select * From MyTable
>  where DateCol > [someDate]
>
>
> New let's say we are doing a join with the above MyView. Let's say it is
> something like this:
>
> Select x.Col1, x.Col2, x.Col3,
>     v.*
> From TABLEX x
>  JOIN MyView v on (v.someCol = x.someCol)
>
> If I wanted to optimize the above, do I want an index on:
> 1) v.someCol
> 2) x.SomeCol
> 3) v.DateCol
> 4) v.someCol and v.DateCol
> 5) Some other combination of above

As long as your views are simple - not aggregated, not grouped,
and (I think) without unions, they disappear during query compilation
and optimization is done as if the query were written with the view
expanded. In this case, I'd put an index on MyTable.SomeCol,
MyTable.DateCol and TableX.SomeCol. The MyTable.DateCol
index would always be used in the query above, and having
indexes on both SomeCol columns lets the optimizer choose the
best join order based on selectivity and table size.

>
> Last Q...
> Is it the case that the Query Optimizer will use an index, even if the
> statistics for that index are very poor? Let's assume the stats are .5 (about as
> bad as they can be). Still it cuts the number of entities roughly in half
> (assuming the column data is split 50/50). So does it use this index? Or are
> their cases where the overhead of using an index means that performance will go
> backward and hence opts to do a natural join instead?

The goal of the optimizer is to reduce the number of pages read because
page reads are the most expensive database operation. An index on
a field that has two values evenly distributed is likely to reference every
page for each value, so the optimizer would probably ignore it. But the
threshold at which the optimizer discards an index changes. In the early
days, the people working on the optimizer assumed that database designers
knew what they were doing, and would use indexes even if they seemed
unpromising. Over time, that assumption has been scaled back, and is
likely to change with different versions of Firebird.

Good luck,

Ann