Hi,

> 2. sale (rows 61608)
> --------------------
> company_id smallint
> id integer
> entry_date date
> (other columns.....)
> (pk - company_id, id)
> (index - company_id, entry_date)

> Now a query like this

SELECT
P.product_nm, S.bill_no, S.entry_date, SD.quantity,
SUM( ( SD.quantity * SD.rate ) - SD.discount_a ),
'S'
FROM
sale_details SD
JOIN sale S ON (SD.company_id = S.company_id ) AND (SD.parent_id = S.id)
JOIN products P ON (SD.company_id = P.company_id ) AND (SD.product_id =
P.id)
WHERE
((S.company_id = 1 ) AND
(S.entry_date >= '1.4.04' ) AND (S.entry_date <= '30.4.04' ))
GROUP BY
S.entry_date, S.bill_no, P.product_nm, SD.quantity

> takes around 42 seconds to execute and using the
> plan
> PLAN SORT (JOIN (SD INDEX
> (FK_SALE_DETAILS_TO_SALE),S INDEX (PK_SALE),P INDEX
> (PK_PRODUCTS))).
>
> Is there any other way to speed up this query ? If
> an index is required,
> on what colunm and which table ?

The index (company_id, entry_date) you have on table sale isn't selected
although it's a interesting one for this query. The problem is that the
optimizer gives higher priority on foreign-keys for determing the join
ORDER.

a workaround which probably will do it :

SELECT
p.product_nm, s.bill_no, s.entry_date, sd.quantity,
SUM((sd.quantity * sd.rate) - sd.discount_a),
'S'
FROM
sale s
LEFT JOIN sale_details sd ON (sd.company_id = s.company_id and
sd.parent_id = s.id)
JOIN products p ON (sd.company_id = p.company_id and sd.product_id = p.id)
WHERE
((s.company_id = 1) and
(s.entry_date >= '1.4.04') and (s.entry_date <= '30.4.04'))
GROUP BY
s.entry_date, s.bill_no, p.product_nm, sd.quantity

With the LEFT JOIN i force the JOIN ORDER and the index (company_id,
entry_date) should be chosen.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info