Help needed

nomuken · Web Design Newbie Joined: Oct 18, 2005 Posts: 17

Hello Friends,

I am having a report in my application, where i need to display a customer report which has various criaterias on it.

Query seems to take too much time for execution.
There are few reasons i can find out...
1. There are more than 18,000 customers
2. 20,000 customer address where i need to have left join
3. 10,000 document records where i need to have left join
and some functions like SUM, DISTINCT, CONCAT_WS, etc...

I have prepared a query as :

SELECT DISTINCT customers.ROWID, CONCAT_WS( " ", customers.salutation, customers.firstname, customers.lastname ) AS customername, customers_address.cstreet, CONCAT_WS( " ", customers_address.czip, customers_address.clocation ) AS customerziplocation, customers_address.ccountry, customers_address.ccompany AS customercompany, sum(
IF (
documents.totalAmount IS NULL , 0, documents.totalAmount
) ) AS totamount

FROM customers

LEFT JOIN documents ON ( documents.custID = customers.ROWID AND documents.docType = 'Invoice' AND documents.numbered = '1' AND documents.status <> 'replace' )

LEFT JOIN selcriterias_customer ON ( selcriterias_customer.customerID = customers.ROWID )

LEFT JOIN customers_address ON ( customers_address.customerID = customers.ROWID AND customers_address.addressType = 'Private' )

INNER JOIN users ON ( users.ROWID = customers.createdBy )

WHERE customers.isdeleted =0 AND customers.mandateID =1
GROUP BY customers.ROWID
HAVING sum(
IF (
documents.totalAmount IS NULL , 0, documents.totalAmount
) ) >= '1200' AND sum(
IF (
documents.totalAmount IS NULL , 0, documents.totalAmount
) ) <= '12001'

I doubt is there anything doing with lot's of left joins for performace?
what i can have to optimize the speed?