Marius, > 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))
I would suspect that the current indexes are too "broad" for the system to be able to narrow the criteria. What is the selectivity/uniqueness of the POSTSTATUS, SALE_DATE and CASHCREDIT fields/columns? Personally, I would be inclined to create a compound index on these three fields in the Sales table, where the order to the fields/columns in the index definition would go from least unique to most specific (ie. POSTSTATUS + CASHCREDIT + SALE_DATE or CASHCREDIT + POSTSTATUS + SALE_DATE). That type of index would allow for the SALES entries to be narrowed quickly, leaving the SKU evaluation as a secondary/notional evaluation. Sean P.S. You might also want to create an compound index on SALESITEMS on SKU + LINE Code (in that order, from least to most unqiue). Depending on the number of Line Code entries per Sales that index could almost be considered a unique relationship for each SALES entry.
