Help With Speeding Up Nested Select Query

I have the below select query I perform, however it is alarmingly slow against even ~6000 records and am curious if there is an alternate way to build the query?

select sum(Q.R) 
from (select 
        T.drawing_no,
        max(T.drawing_rev),
        T.R 
      from (select 
              drawing_no,
              drawing_rev,
              sum(price_total) as R 
            from data_pipe_drawing 
            where drawing_status="IP" 
            group by 
              drawing_no,
              drawing_rev) as T 
      group by T.drawing_no) as Q

In English the query finds all the drawings with a status of IP, rolls up the prices (price_total) by drawing_no+drawing_rev and then returns only the price_total of the highest revision for each drawing and rolls it all up for a grand price_total.

Thus before hitting the max(T.drawing_rev) if the query held:

 Drawing A Rev 1 Total Cost $100
 Drawing A Rev 0 Total Cost $50
 Drawing B Rev 0 Total开发者_如何学编程 Cost $200

The Drawing A Rev 0 is dropped from the grand total. The grand total is $300.

Hope this makes sense... thanks for any advice!

As per my comment, I don't feel safe with the query you wrote, I'd do something with a correlated sub-query, like this...

  SELECT
--  drawing_no,
    SUM(price_total)
  FROM
    data_pipe_drawing AS data
  WHERE
    drawing_status="IP" 
    AND drawing_rev = (SELECT MAX(drawing_rev) FROM data_pipe_drawing WHERE drawing_status = "IP" AND drawing_no = data.drawing_no)
--GROUP BY
--  drawing_no

-- Commented lines useful for testing individual drawings

Then have an index on (drawing_status, drawing_no, drawing_rev)

Or possibly even this...

  SELECT
    SUM(data.price_total)
  FROM
    data_pipe_drawing AS data
  INNER JOIN
  (
    SELECT
      drawing_status,
      drawing_no,
      MAX(drawing_rev) AS drawing_rev
    FROM
      data_pipe_drawing
    GROUP BY
      drawing_status,
      drawing_no
  )
    AS lookup
      ON  lookup.drawing_number = data.drawing_number
      AND lookup.drawing_rev    = data.drawing_rev
      AND lookup.drawing_status = data.drawing_status
  WHERE
    data.drawing_status="IP" 

EDIT

Changed the JOIN based solution to possibly be friendlier for multiple drawing states, pending mor einfor from the OP.

Try seperating your query with temporary table,

create temporary table tmp_table select 
              drawing_no,
              drawing_rev,
              sum(price_total) as R 
            from data_pipe_drawing 
            where drawing_status="IP" 
            group by 
              drawing_no,
              drawing_rev;

select sum(Q.R) 
from (select 
        T.drawing_no,
        max(T.drawing_rev),
        T.R 
      from tmp_table T 
      group by T.drawing_no) as Q