buppcpp wrote:

>Ex. SELECT distinct store_no from mytable; (Does a table scan)
>
>mytable has 2.1 million records with an index on store_no.
>It takes 3.32 minutes to run.
>
>

Using Firebird CS 1.5.2 on Fedora Core 3 running on AMD Athlon64 with 1
GB RAM. I have a table with the following structure:

CREATE TABLE TRI (
TRI_PK NUMERIC(18, 0),
DB_AMT NUMERIC(18, 4),
CR_AMT NUMERIC(18, 4),
TRH_PK NUMERIC(18, 0),
ACCOUNT_NO CHAR(10) NOT NULL,
TRX_DESC VARCHAR(50),
SPECIAL_JOURNAL INTEGER);

Current record count: 1m

ACCOUNT_NO stored in XXXX.XX.XX format. The table have an index on
field ACCOUNT_NO. I do SELECT DISTINCT SUBSTRING(ACCOUNT_NO FROM 1 FOR
4) FROM TRI form my workstation, it's return 23 records after 2s 766ms.
May be there are another factors that causing your query very slow (OS,
DAC, etc. etc).


HJ