List:General Discussion« Previous MessageNext Message »
From:Cory Robin Date:May 30 2006 6:04am
Subject:Re: ugly SQL for a report...
View as plain text  
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

Thread
ugly SQL for a report...Cory29 May
  • Re: ugly SQL for a report...Jochem van Dieten29 May
  • Re: ugly SQL for a report...Cory Robin29 May
    • Re: ugly SQL for a report...Jochem van Dieten29 May
      • Re: ugly SQL for a report...Cory Robin30 May
    • Re: ugly SQL for a report...Jochem van Dieten29 May
    • Re: ugly SQL for a report...Jochem van Dieten29 May
    • Re: ugly SQL for a report...Peter Brawley30 May