Hi Paul!

I'd say the ideal plan would be something like:
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (WI INDEX
(IDX_WORKSITEM_SALES_ORDER, IDX_WORKSITEM_SALES_ITEM), WH INDEX
(RDB$PRIMARYxx)),WJ INDEX (RDB$PRIMARY53)),WR INDEX
(RDB$PRIMARY56)),ST INDEX (RDB$PRIMARY25)),WS INDEX(WORKSSCHEDULE_NUMB))

Though I don't know the selectivity of your indexes, and you may well be
right in that NATURAL is sensible for WS if you have 20-30 records in
the table and NUMB isn't very selective. Though generally, having a
NATURAL at the end of a plan is very bad (it is less of a problem for
the first table in the plan).

So, my questions are: How selective are WORKSITEM.SALES_ORDER and
WORKSITEM.SALES_ITEM, and have you defined (ascending) indexes for these
fields?

If you have these indexes and your problem persevere, you can force
Firebird (well, almost force, in theory Firebird may choose to use
another NATURAL) to put WI before WH in your plan by doing a very small
change:

JOIN WORKSITEM WI ON WH.NUMB = WI.NUMB+0

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

Sorry, I don't know of any articles. Just use some common sense - if
fields are selective and likely to be in JOIN or WHERE clauses, then
index them. If not, odds are that you don't need to index them.

HTH,
Set

paultugwell wrote:

> 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?