monster query

EXPLAIN  SELECT
  sales_date
  ,  merchant
  , merchant_name
  , package_class AS package__class
  , rate_type AS rate__type
  , package_desc AS package__desc
  , SUM(order_grouped_shipping) + SUM(ticket_shipping) AS net_shipping
  , SUM(ticket_fee) AS fee_amount
  , SUM(order_grouped_cart_adjustment) AS net_cadj
  , (
    SUM(order_grouped_shipping) + SUM(ticket_shipping) + SUM(ticket_fee) + SUM(order_grouped_fee) + SUM(order_grouped_cart_adjustment) + SUM(ticket_tax) + SUM(ticket_retail)
  ) AS order_total
  , SUM(ticket_count) AS ticket_count
  , SUM(ticket_tax) AS tax_amount
  , SUM(ticket_retail) AS retail
  , SUM(order_grouped_fee) AS refund_adjustment
FROM
  (
SELECT
  DATE_FORMAT(o.local_order_date,'%m-%d-%Y')AS sales_date
  , merchant
  , merchant_name
  , package_class
  , rate_type
  , package_desc
  , oi.master_ticket_id
  , oi.ticket_id
  , SUM(
    IF(
      oi.type = 'T'
      , IF(oi.action = 'R', - 1, 1)
      , 0
    )
  ) AS ticket_count
  , SUM(
    IF(
      oi.type = 'S'
      AND ISNULL(oi.ticket_id)
      , oi.retail_amount
      , 0
    )
  ) * (
    order_stats.package_tickets / order_stats.order_tickets
  ) AS order_grouped_shipping
  , SUM(
    IF(
      oi.type = 'S'
      AND NOT ISNULL(oi.ticket_id)
      , oi.retail_amount
      , 0
    )
  ) AS ticket_shipping
  , SUM(
    IF(
      oi.type = 'F'
      AND ISNULL(oi.ticket_id)
      , oi.retail_amount
      , 0
    )
  ) * (
    order_stats.package_tickets / order_stats.order_tickets
  ) AS order_grouped_fee
  , SUM(
    IF(
      oi.type = 'F'
      AND NOT ISNULL(oi.ticket_id)
      , oi.retail_amount
      , 0
    )
  ) AS ticket_fee
  , 0 * SUM(
    IF(
      oi.type = 'A'
      AND ISNULL(oi.ticket_id)
      , oi.retail_amount
      , 0
    )
  ) * (
    order_stats.package_tickets / order_stats.order_tickets
  ) AS order_grouped_cart_adjustment
  , 0 * SUM(
    IF(
      oi.type = 'A'
      AND NOT ISNULL(oi.ticket_id)
      , oi.retail_amount
      , 0
    )
  ) AS ticket_cart_adjustment
  , SUM(
    IF(
      oi.type = 'T'
      AND NOT ISNULL(oi.ticket_id)
      , th.tax_amount * IF(oi.action = 'R', - 1, 1)
      , 0
    )
  ) AS ticket_tax
  , SUM(
    IF(
      oi.type = 'T'
      , th.retail_amount * IF(oi.action = 'R', - 1, 1)
      , 0
    )
  ) AS ticket_retail
FROM
  order_items oi
  INNER JOIN ticket_order_header o
    ON oi.master_ticket_id = o.master_ticket_id
  inner join merchant m1 on o.merchant_id = m1.merchant_id
  LEFT JOIN ticket_header th
    ON (oi.ticket_id = th.ticket_id OR oi.type = 'A')
    AND oi.master_ticket_id = th.master_ticket_id
  INNER JOIN
    (SELECT
      o2.master_ticket_id
        , th2.ticket_id
      , IF(
        get_ticket_count_by_order (o2.master_ticket_id) = 0
        , COUNT(DISTINCT th2.ticket_id)
        , COUNT(
          DISTINCT IF(
            EXISTS
            (SELECT
              *
            FROM
              merchant_package_characs mpc
            WHERE mpc.merchant_id = th2.merchant_id
              AND mpc.package_id = th2.package_id
              AND mpc.name = 'package_type'
              AND mpc.value = 'Merchandise')
            , NULL
            , th2.ticket_id
          )
        )
      ) AS package_tickets
      , IF(
        get_ticket_count_by_order (o2.master_ticket_id) = 0
        , COUNT(DISTINCT th2.ticket_id)
        , get_ticket_count_by_order (th2.master_ticket_id)
      ) AS order_tickets
      , o2.merchant_id AS merchant
      , m.name AS merchant_name
      , package_class
      , customer_type_name AS rate_type
      , package_desc
    FROM
      ticket_order_header o2
      INNER JOIN ticket_header th2
        ON o2.master_ticket_id = th2.master_ticket_id
      INNER JOIN package_header
        ON th2.package_id = package_header.package_id
      INNER JOIN customer_type
        ON th2.customer_type = customer_type.customer_type
        INNER JOIN merchant m ON o2.merchant_id = m.merchant_id
    WHERE (o2.merchant_id IN (6234) or m.parent_econsignment_merchant_id IN (0) )
       AND order_date >= '2012-01-01'
      AND order_date < DATE_ADD('2012-10-31', INTERVAL 1 DAY)
    GROUP BY o2.master_ticket_id, th2.ticket_id) order_stats
      ON order_stats.master_ticket_id = o.master_ticket_id
      AND (
        (
          ISNULL(order_stats.ticket_id)
          AND ISNULL(th.ticket_id)
        )
        OR order_stats.ticket_id = th.ticket_id
      )
  WHERE (o.merchant_id IN (6234) or m1.parent_econsignment_merchant_id IN (0))
  AND o.local_order_date >= '2012-01-01'
  AND o.local_order_date < DATE_ADD('2012-10-31', INTERVAL 1 DAY)
GROUP BY oi.master_ticket_id
  , oi.ticket_id
  ) AS main
  GROUP BY main.merchant
  , main.package_class
  , main.rate_type
  , main.package_desc
 LIMIT 0, 1500