I made a minor mistake in the query when I reorganized it, I made
assumptions about WJ.STATUS and WR.ITEM that I couldn't; you probably
already noticed it.

If the problem is that WI.SALES_ORDER and WI.SALES_ITEM are not very
selective, you could use a compound key with the field most commonly
used in queries first. If this combination is unique, then a constraint
would be good as that's implemented by an index.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
Sent: Wednesday, January 30, 2008 1:27 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Indices

Reorganizing your query:

SELECT
*
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
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
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
WH.JOB_TYPE <> 'H' AND
WH.STATUS <> 'D' AND
WI.SALES_ORDER = :SALES_ORDER_H AND
WI.SALES_ITEM = :SALES_SUFFIX_H AND
WI.STATUS <> 'D' AND
NOT (WI.STATUS = 'C' AND WI.PROD_WT = 0) AND
WJ.STATUS <> 'C' AND
WR.ITEM = 1

First, all the fields in the join section of the query should be foreign
keys pointing to the table they are joining against.
Second, WORKSHEADER will always incur a full table scan, as I don't
think an index will help with the condition "<> 'H'". Even if it is
possible, how selective would an index on this field be?
Perhaps you could add another condition, such as excluding all work
orders that are older than a year?

I don't understand the lack of an index for WORKSSCHEDULE, does
WORKSSCHEDULE.NUMB have a foreign key pointing to WORKSHEADER.NUMB?

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of paultugwell
Sent: Wednesday, January 30, 2008 12:47 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Indices

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?

Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.