Hi,
Is it possible to optimize the following select query? I make use of Firebird 2.5.2.26540 in Super Server mode. The query looks like this: SELECT Sum(SALEITEMS.QUANTITY), Sum(SALEITEMS.QUANTITY * SALEITEMS.COSTPRICEEX) FROM SALES SALES INNER JOIN SALEITEMS SALEITEMS ON SALES.LINECODE = SALEITEMS.LINECODE WHERE SALES.POSTSTATUS = 'Posted' AND SALES.SALE_DATE BETWEEN :vStartDate AND :vEndDate AND SALEITEMS.SKU = :vSKU AND SALES.CASHCREDIT = 'Cash' The plan being utilized is as follow: PLAN JOIN (SALEITEMS INDEX (I_SALEITEMS_SKU), SALES INDEX (I_SALES_ULINECODE)) Would the result not be much faster if I can get this query to utilize the index on the SALES table on the SALE_DATE field (which exists and is active)? Looking at the plan that is being utilised I get the feeling that all records with the particular SKU (:vSKU) is first selected (Detail table), and there can be millions of these, whereas only hundreds or thousands of records would exist if the plan would first get the subset of sales records based on the master table SALES, where the SALE_DATE are between 2 dates? The SALES and SALEITEMS tables has a 1:1M relationship, a typical Master Detail relationship, with the LINECODE field being the link between the two tables. Any advise much appreciated. Regards Marius J. Labuschagne
