Hi,

> OK, here's the info you requested:
> (NOTE: This is now on version 1.5.2, version 2.0 wasn't much better.)
>
> TABLE LAYOUT:
>
> SKU VARCHAR(16) (PK)
> STORE_NO INTEGER (PK) (this field also has an individual index on
> it)
> COST DECIMAL(15, 4)
> PRICE DECIMAL(15, 4)
> .
> (15 other fields are also in this table)

Also BLOB fields?

> Primary Key Stats (PK_SKU_STORE). 0.0000004474044
> Store_No Key Stats (IDX_STORE). 0.5000000000000

Store_No contains a lot of duplicates, is the index really needed?

> Database header page information:
> Page size 8192
> Page buffers 0

Set Page buffers to the number you want FB to use as maximum memory. For example for 82MB set
buffers to 10000

> Queries:
> 1. SELECT store_no FROM mytable GROUP BY 1;
> (PLAN MYTABLE IDX_STORE)
> Prepare: 20 ms Execute: 2 min 37 s 657 ms Fetch: 2 min 30 s 617 ms

> 2. SELECT DISTINCT store_no FROM mytable;
> PLAN SORT ((MYTABLE NATURAL))
> Prepare: 30 ms Execute: 1 min 24 s 141 ms Fetch: 180 ms
> (NOTE: The above query was ran right after the first query, so this
> result is with some caching involved. It much worst with a fresh
> start.)

Shouldn't store_no be a foreign key to another table so you can run SELECT * FROM StoreNoTable?

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