Svein Erling Tysvaer wrote:

> This index may cause things to slow down when doing updates and deletes due
> to its very low selectivity. Whenever you index such a field, make sure you
> index it using (<low selectivity field>, <pk>). Since archived status is
> the highest number, your index will work well if it is ASCending (which is
> the default).

thanks for that advice. The guy maintaining the code originally did it in
paradox and found that adding indexes led to the import from text file into the
database running slower. I've been saying that the same shouldn't apply to FB.

> Too many indexes may be as bad as too few indexes. But as Helen said, we
> must see the SQL, the generated plan, index definitions and preferably a
> bit information about your database (e.g. number of records in table and
> number of records with status <=3). Until then, we can only give you
> general advice - which may or may not be appropriate.

Here's a listing of (the essentials of) the table with its indexes:

Table SAWMEMBERS has 32 fields.
1* AUTO_NUMBER Integer REQ
2: JOB_NUMBER String 8
3: TRUSS_NUMBER String 10
4: MEMBER_ID String 8
5: STATION_ID Smallint
6: SAW_NUMBER Smallint
...
24: LEFT_MITRE_ANGLE Smallint
25: RIGHT_MITRE_ANGLE Smallint
26: START_TIME DateTime
27: END_TIME DateTime
28: MEMBER_DURATION Float
29: USER_NAME String 40
30: MASTERSAW_REJECTED Smallint
31: DATE_ARCHIVED DateTime
32: STATUS Smallint
Index 0: Name=RDB$PRIMARY10 Fields=AUTO_NUMBER Options=Primary,Unique
Index 1: Name=NO_DOUBLE_MITRES2 Fields=NO_DOUBLE_MITRES
Index 2: Name=STATION_ID_SMIDX Fields=STATION_ID
Index 3: Name=SM_STATUS Fields=STATUS
Index 4: Name=SM_JOB_NUMBER Fields=JOB_NUMBER
Index 5: Name=SM_START_TIME Fields=START_TIME

I haven't looked at the big database, but on a fair sized on that I am testing
on the number of SAWMEMEBRS records is 906,808; WHERE STATUS <= 3 it's 21,005.

Actually, I find that these queries run fast in most cases. It may be because we
has two threads running and there is some waiting or something going on.

Regards,

Mark