MYSQL Convert rows to columns performance problem
- by Tarski
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?
