I have the following query that is running VERY slowly. Anyone have
any suggestions?
---
SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number,
pnr.created_by, GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY
name_last DESC SEPARATOR '<br>') names, (SELECT SUM(pf.base_fare*(SELECT
COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_fares
pf WHERE pf.ID_pnr=pnr.ID ) base_fare, (SELECT SUM(pf.tax*(SELECT
COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_fares
pf WHERE pf.ID_pnr=pnr.ID ) tax , (SELECT SUM(psf.segfee_amount*(SELECT
COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM
pnr_segments_fees psf INNER JOIN pnr_segments pss ON
psf.ID_segments=pss.ID WHERE psf.segfee_code='XF' AND pss.ID_pnr=pnr.ID
) XF ,(SELECT SUM(amount_value) service_fees FROM pnr_service_fees WHERE
ID_pnr=pnr.ID) services ,(SELECT SUM(amount) FROM pnr_payments_credits
WHERE ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='sale')
payments, (SELECT SUM(amount) FROM pnr_payments_credits WHERE
ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='credit') credits
FROM pnr INNER JOIN pnr_passengers pp ON pnr.ID=pp.ID_pnr WHERE
pnr.reservationdatetime>='2000-05-29 00:00:00' AND
pnr.reservationdatetime<='2006-05-29 23:59:59' GROUP BY pnr.ID ORDER BY
reservationdatetime;
---
I can see that one issue is the repeated use of SELECT COUNT... Is
there any way to get the query to only do this once?
ANY help will be greatly appreciated.
We're running MySQL 5
Cory.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
