Slow Query Help

[imperium2335]

Hi,

 

I am having a problem with a part of my query:

 

"SELECT SQL_CALC_FOUND_ROWS 
						jobs.id AS jobId, enquiries.id AS profId, users.username, entity_details.name AS entityName,
						 branches.name AS branch, orderNumber,
						 entity_vat_numbers.vatCode, entity_account_numbers.accountNumber,

						 (SELECT symbol
						  FROM parts_trading, currencies
						  WHERE parts_trading.enquiryRef = enquiries.id
						  AND parts_trading.sellingCurrency = currencies.id
						  LIMIT 1)
						 AS symbol,

						 (SELECT SUM(quantity) FROM parts_trading WHERE enquiryRef = enquiries.id)
						 -
						 COALESCE((SELECT SUM(quantity) FROM invoices_out_reference WHERE jobRef = jobs.id), 0)
						 AS differentPartsCount,

						 (SELECT SUM(quantity*sellingNet) FROM parts_trading WHERE parts_trading.enquiryRef = enquiries.id)
						 -
						 COALESCE(
						 	(SELECT SUM(invoices_out.net)
							 FROM invoices_out, invoices_out_reference
							 WHERE invoices_out_reference.invoiceRef = invoices_out.id
							 AND invoices_out_reference.jobRef = jobs.id
						 	)
						 , 0)
						 AS netRemaining,

						 (SELECT SUM(quantity*sellingVat) FROM parts_trading WHERE parts_trading.enquiryRef = enquiries.id)
						 -
						 COALESCE(
						 	(SELECT SUM(invoices_out.vat)
							 FROM invoices_out, invoices_out_reference
							 WHERE invoices_out_reference.invoiceRef = invoices_out.id
							 AND invoices_out_reference.jobRef = jobs.id
						 	)
						 , 0)
						AS vatRemaining,

						(SELECT SUM(quantity*(sellingNet+sellingVat)) FROM parts_trading WHERE parts_trading.enquiryRef = enquiries.id)
						 -
						 COALESCE(
						 	(SELECT SUM(invoices_out.net+invoices_out.vat)
							 FROM invoices_out, invoices_out_reference
							 WHERE invoices_out_reference.invoiceRef = invoices_out.id
							 AND invoices_out_reference.jobRef = jobs.id
						 	)
						 , 0)
						AS totalRemaining

						 FROM jobs, enquiries, users, branches, entity_details
						 LEFT JOIN entity_account_numbers ON entity_details.id = entity_account_numbers.entityRef
						 LEFT JOIN entity_vat_numbers ON entity_details.id = entity_vat_numbers.entityRef
						 WHERE enquiries.traderRef = users.id
						 AND jobs.enquiryRef = enquiries.id
						 AND users.branchRef = branches.id
						 AND (SELECT SUM(quantity) FROM parts_trading WHERE enquiryRef = enquiries.id)
						 -
						 COALESCE((SELECT SUM(quantity) FROM invoices_out_reference WHERE jobRef = jobs.id), 0) > 0
						 AND enquiries.entityRef = entity_details.id"

 

I know for certain that it is the part:

 

"AND (SELECT SUM(quantity) FROM parts_trading WHERE enquiryRef = enquiries.id)
						 -
						 COALESCE((SELECT SUM(quantity) FROM invoices_out_reference WHERE jobRef = jobs.id), 0) > 0"

 

That is causing the problem.

 

Unfortunately this is a vital part of my query used for not displaying rows that have quantities matching the sum of what is in the other table .

 

Is there are more efficient way to get the same result than that part of the query?

[imperium2335]

Sorry guys my bad, I just realised I didn't put any indexes on my reference table columns...