Slightly new query.. Here's the new query and results of an explain..
I'm thinking that some indexing would help.. However, I don't really
know where to start.
---
EXPLAIN
SELECT
pnr.ID ID_pnr,
pnr.reservationdatetime,
pnr.conf_number,
pnr.created_by,
( SELECT @pp_cnt := COUNT(1)
FROM pnr_passengers pp
WHERE pp.ID_pnr = pnr.ID
) AS pp_count,
GROUP_CONCAT(pp.name_last,', ',pp.name_first
ORDER BY name_last DESC SEPARATOR '<br>') names,
(SELECT SUM(pf.base_fare*( @pp_cnt ))
FROM pnr_fares pf
WHERE pf.ID_pnr=pnr.ID ) base_fare,
(SELECT SUM(pf.tax*( @pp_cnt ))
FROM pnr_fares pf
WHERE pf.ID_pnr=pnr.ID ) tax ,
(SELECT SUM(psf.segfee_amount*( @pp_cnt ))
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(psf.segfee_amount*( @pp_cnt ))
FROM pnr_segments_fees psf
INNER JOIN pnr_segments pss
ON psf.ID_segments=pss.ID
WHERE psf.segfee_code='AY'
AND pss.ID_pnr=pnr.ID ) AY ,
(SELECT SUM(psf.segfee_amount*( @pp_cnt ))
FROM pnr_segments_fees psf
INNER JOIN pnr_segments pss
ON psf.ID_segments=pss.ID
WHERE psf.segfee_code='FS'
AND pss.ID_pnr=pnr.ID ) FS ,
(SELECT SUM(psf.segfee_amount*( @pp_cnt ))
FROM pnr_segments_fees psf
INNER JOIN pnr_segments pss
ON psf.ID_segments=pss.ID
WHERE psf.segfee_code='ZP'
AND pss.ID_pnr=pnr.ID ) ZP ,
(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>='2006-05-01 00:00:00'
AND pnr.reservationdatetime<='2006-05-29 23:59:59'
GROUP BY pnr.ID
ORDER BY reservationdatetime
---
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: pp
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5326
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: pnr
type: eq_ref
possible_keys: PRIMARY,both_id,resdt
key: PRIMARY
key_len: 8
ref: S6_AirlineData.pp.ID_pnr
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 11
select_type: DEPENDENT SUBQUERY
table: pnr_payments_credits
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4865
Extra: Using where
*************************** 4. row ***************************
id: 10
select_type: DEPENDENT SUBQUERY
table: pnr_payments_credits
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4865
Extra: Using where
*************************** 5. row ***************************
id: 9
select_type: DEPENDENT SUBQUERY
table: pnr_service_fees
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 701
Extra: Using where
*************************** 6. row ***************************
id: 8
select_type: DEPENDENT SUBQUERY
table: psf
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 20248
Extra: Using where
*************************** 7. row ***************************
id: 8
select_type: DEPENDENT SUBQUERY
table: pss
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 16
ref: S6_AirlineData.psf.ID_segments,S6_AirlineData.pnr.ID
rows: 1
Extra: Using index
*************************** 8. row ***************************
id: 7
select_type: DEPENDENT SUBQUERY
table: psf
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 20248
Extra: Using where
*************************** 9. row ***************************
id: 7
select_type: DEPENDENT SUBQUERY
table: pss
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 16
ref: S6_AirlineData.psf.ID_segments,S6_AirlineData.pnr.ID
rows: 1
Extra: Using index
*************************** 10. row ***************************
id: 6
select_type: DEPENDENT SUBQUERY
table: psf
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 20248
Extra: Using where
*************************** 11. row ***************************
id: 6
select_type: DEPENDENT SUBQUERY
table: pss
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 16
ref: S6_AirlineData.psf.ID_segments,S6_AirlineData.pnr.ID
rows: 1
Extra: Using index
*************************** 12. row ***************************
id: 5
select_type: DEPENDENT SUBQUERY
table: psf
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 20248
Extra: Using where
*************************** 13. row ***************************
id: 5
select_type: DEPENDENT SUBQUERY
table: pss
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 16
ref: S6_AirlineData.psf.ID_segments,S6_AirlineData.pnr.ID
rows: 1
Extra: Using index
*************************** 14. row ***************************
id: 4
select_type: DEPENDENT SUBQUERY
table: pf
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 6459
Extra: Using where
*************************** 15. row ***************************
id: 3
select_type: DEPENDENT SUBQUERY
table: pf
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 6459
Extra: Using where
*************************** 16. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: pp
type: index
possible_keys: NULL
key: PRIMARY
key_len: 16
ref: NULL
rows: 5326
Extra: Using where; Using index
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
