Subject Indices
Author paultugwell
I am running this piece of SQL

SELECT WH.NUMB, WI.SX, WH.COIL, WH.COIL_SX, WH.JOB_TYPE, WH.STATUS AS
HSTATUS, WI.STATUS AS ISTATUS, WJ.STATUS AS JSTATUS,
ST.WEIGHT_STK - ST.WEIGHT_QUA AS STOCK_WEIGHT, WS.SCHEDULE_NO
FROM WORKSHEADER WH
JOIN WORKSITEM WI ON WH.NUMB = WI.NUMB
LEFT JOIN WORKSRECEIPT_JOB WJ ON WJ.NUMB = WH.NUMB AND WJ.JOB_NO =
WI.JOB_NO AND WJ.STATUS <> 'C'
LEFT JOIN WORKSRECEIPT_ITEM WR ON WR.NUMB = WJ.NUMB AND WR.REC =
WJ.REC AND WR.JOB_NO = WJ.JOB_NO AND WR.JOB_SX = WJ.JOB_SX AND WR.SX
= WI.SX AND WR.ITEM = 1
LEFT JOIN STOCK ST ON ST.COIL = WH.COIL AND ST.COIL_SX = WR.COIL_SX
LEFT JOIN WORKSSCHEDULE WS ON WS.NUMB = WH.NUMB
WHERE WI.SALES_ORDER = :SALES_ORDER_H AND WI.SALES_ITEM
= :SALES_SUFFIX_H
AND WH.JOB_TYPE <> 'H' AND WI.STATUS <> 'D' AND WH.STATUS <> 'D' AND
NOT (WI.STATUS = 'C' AND WI.PROD_WT = 0)

which results in this plan

PLAN JOIN (JOIN (JOIN (JOIN (JOIN (WH NATURAL,WI INDEX
(IDX_WORKSITEM_NUMB)),WJ INDEX (RDB$PRIMARY53)),WR INDEX
(RDB$PRIMARY56)),ST INDEX (RDB$PRIMARY25)),WS NATURAL)

This causes a problem as it is slow. If I look at the statistics, the
WORKSHEADER (WH) seems to be the culprit as a large number of records
are being read without using an index. The PK on this table is NUMB.
I have tried adding indices to all of the fields in the table
WORKSHEADER (WH) that are in the join and where clauses as
individual indicies for each field, and I have tried adding indicies
for combinations of fields, but FB always uses the natuaral index.
The natural index on WORKSSCHEDULE (WS) shouldn't cause a problem as
there are only ever 20-30 records in this table.

Can anybody help me?

Can anybody point me to some articles that explain how to decide what
indices are needed on a table?