I am doing a query that converts rows to columns similar to this post but have encountered a performance problem. Here is the query:-

SELECT
    Info.Customer,
    Answers.Answer,
    Answers.AnswerDescription,
    Details.Code1,
    Details.Code2,
    Details.Code3
FROM
    Info
    LEFT OUTER JOIN Answers
    ON Info.AnswerID = Answers.AnswerID
    LEFT OUTER JOIN
    (SELECT
      ReferenceNo,
      MAX(CASE DetailsIndicator WHEN 'cde1' THEN DetailsCode ELSE NULL END ) Code1,
      MAX(CASE DetailsIndicator WHEN 'cde2' THEN DetailsCode ELSE NULL END ) Code2,
      MAX(CASE DetailsIndicator WHEN 'cde3' THEN DetailsCode ELSE NULL END ) Code3
    FROM DetailsData
    GROUP BY ReferenceNo) Details
    ON Info.ReferenceNo = Details.ReferenceNo

There are less than 300 rows returned, but the Details table is about 180 thousand rows. The query takes 45 seconds to run and needs to take only a few seconds. When I type show processlist; into MYSQL it is hanging on "Sending Data".

Any thoughts as to what the performance problem might be?