Hi,

>> Drop the index on Store_No, the selectivity is too poor for the index
>> to be useful (your GROUP BY query should execute somewhat faster
>> without this index, probably equal to your DISTINCT query).
>
> And why doesn't FB automatically ignore an index with such bad
> selectivity in this situation? Is there *any* situation where this index
> should actually be used? I doubt it. So the engine should ignore it.

Yes, there are certainly cituations where the index does help. When a field contains only 2 values,
but 1 value is in only 1% of the records.
For example a table with a boolean field History. When the records are processed by a routine the
HISTORY field is set to true, selecting all records which aren't processed yet by a query using an
index on History is fast.

> This is just one example of a performance pitfall with FB that could
> probably be fixed.

Pitty enough, you can't say that so simple else we would aready fixed that. Good ideas/suggestions
are always welcome to the development team.

Regards,
Arno Brinkman
ABVisie

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

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

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