How to Optimize below query

Question

SELECT
    t1.theater_id,
    sum(t2.full_sale_price * t1.full_tickets + 
        t2.half_sale_price * t1.half_tickets) as Gross,
    sum(t2.full_cost_price * t1.full_tickets +
        t2.half_cost_price * t1.half_tickets) as Cost,
    Round(sum((t2.full_sale_price * t1.full_tickets + 
               t2.half_sale_price * t1.half_tickets) * t3.rate) / 100,
         2) as IPG,
         (sum(t2.full_sale_price * t1.full_tickets + 
              t2.half_sale_price * t1.half_tickets) 
              - Round(sum((t2.full_sale_price * t1.full_tickets + 
              t2.half_sale_price * t1.half_tickets) * (CASE
                WHEN ISNULL(t3.rate) THEN 0
                ELSE t3.rate
                END)) / 100,
            2)) as NetRev,
    CASE
        WHEN (t1.method = '2') THEN sum(0.00)
        ELSE 0
    END as sms,
    sum((CASE
        WHEN (t4.type = '1') THEN ((t1.full_tickets * 2) + (t1.half_tickets))
        ELSE (t1.full_tickets + t1.half_tickets)
    END) * 4.00) as maintenance,
    sum((((t2.full_sale_price * t1.full_tickets + 
           t2.half_sale_price * t1.half_tickets) 
           - (Round((t2.full_sale_price * t1.full_tickets + 
                     t2.half_sale_price * t1.half_tickets) * (CASE
                WHEN ISNULL(t3.rate) THEN 0
                ELSE t3.rate
            END) / 100,
            2))) - (t2.full_cost_price * t1.full_tickets + 
                    t2.half_cost_price * t1.half_tickets)) 
                    - (CASE
                       WHEN (t1.method = '2') THEN 0.00
                       ELSE 0
                       END) 
                    - (CASE
                       WHEN (t4.type = '1') THEN ((t1.full_tickets * 2) +
                                                  (t1.half_tickets))
                       ELSE (t1.full_tickets + t1.half_tickets)
    END) * 4.00) as admincost,
    sum(CASE
        WHEN (t4.type = '1') THEN ((t1.full_tickets * 2) + (t1.half_tickets))
        ELSE (t1.full_tickets + t1.half_tickets)
    END) as totTk
FROM
    `ratecard_rates` as t2 INNER JOIN
    `reservation` as t1 ON t1.movie_id = t2.movie_id
        AND t1.theater_id = t2.theater_id
        AND t1.showtime_id = t2.showtime_id
        AND t1.category_id = t2.category_id
        AND t1.bx_date = t2.date_apply
        LEFT JOIN
    `paymentgateway` as t3 ON t1.paymentgateway_id = t3.id
        LEFT JOIN
    `theatercategories` as t4 ON t1.category_id = t4.id
WHERE
    t1.status = '1' AND t1.method = '1'
        AND t1.reservation_type = '1'
        AND DATE(`tx_date`) BETWEEN DATE('2012-08-27') AND DATE('2012-08-27')
GROUP BY t1.theater_id

Here is the EXPLAIN

Answer 1

Did you indexed blindly or you first checked you execution plan? CASE..END clause helps a lot but can mess a lot the execution plan sometimes. Check this link it can help you to understand what I'm talking about. One option is creating a stored procedure and splitting this query on smaller querys. Sometimes subqueries can be helpfull, that is another option to try out.

Answer 2

"Using temporary; using filesort" usually means that at this point in the query plan, MySsql decides to write an interim set to disk, which unfortunately means it is not always able to use an index you might have placed on the relevant column.

The only way to get around this, in my experience, is to try and reduce the set size for interim results. Try and isolate the point at which this occurs: for example: replace your whole SELECT with a COUNT(*) to eliminate the CASE bits etc. and see what is producing the 2779 rows which MySql wants to "cache" on disk.

See this answer for more information.