On 5/29/06, Cory Robin wrote:
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;
One way to avoid the repeated COUNT might be to use a nested FROM that
pre-calculates the count as a pseudo-column:
SELECT
pnr_precalc.ID ID_pnr,
pnr_precalc.reservationdatetime,
pnr_precalc.conf_number,
pnr_precalc.created_by,
GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY name_last DESC
SEPARATOR '<br>') names,
(SELECT SUM(pf.base_fare * pnr_precalc.passengercount) FROM pnr_fares
pf WHERE pf.ID_pnr=pnr_precalc.ID ) base_fare,
(SELECT SUM(pf.tax*pnr_precalc.passengercount) FROM pnr_fares pf
WHERE pf.ID_pnr=pnr_precalc.ID ) tax ,
(
SELECT SUM(psf.segfee_amount*pnr_precalc.passengercount)
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_precalc.ID
) XF,
(SELECT SUM(amount_value) service_fees FROM pnr_service_fees WHERE
ID_pnr=pnr_precalc.ID) services ,
(SELECT SUM(amount) FROM pnr_payments_credits WHERE
ID_pnr=pnr_precalc.ID AND bln_payment='1' AND transaction_type='sale')
payments,
(SELECT SUM(amount) FROM pnr_payments_credits WHERE
ID_pnr=pnr_precalc.ID AND bln_payment='1' AND
transaction_type='credit') credits
FROM
(
SELECT
pnr.ID ID_pnr,
pnr.reservationdatetime,
pnr.conf_number,
pnr.created_by,
(SELECT COUNT(1) FROM pnr_passengers pp WHERE
pp.ID_pnr=pnr.ID )
passengercount
FROM
pnr
) pnr_precalc
INNER JOIN pnr_passengers
pp
ON pnr.ID=pp.ID_pnr
WHERE
pnr_precalc.reservationdatetime >= '2000-05-29 00:00:00'
AND
pnr_precalc.reservationdatetime <= '2006-05-29 23:59:59'
GROUP BY
pnr_precalc.ID
ORDER BY
reservationdatetime;
(You probably have to fix this since I can not test it.)
If you see too many loops over pnr_service_fees and
pnr_payments_credits in your explain output you can rework them in the
same way. If this doesn't help, we are going to need a lot more from
the schema and the explain output.
Jochem
