Need some help with a horribly written query

[justinbriggs1]

Hey yall, I got a horrible mess of a query, was wondering if anyone could take a look at it and let me know of any suggestions of how to clean it up. It works, but it runs slowly after too many records are in there. It only involves two tables, but the query basically takes values from rows in the problem_nodes table and turns them into columns for the final result.SELECT * FROM (SELECT urgency,name,phone,location,department,cc,status,case_manager,ip,case_manager_eid,id_problem,id_problem_type,eid_author,title,body,date_created,date_modified FROM problems AS main INNER JOIN ( SELECT id_problem as t_urgency_id_problem, node_value AS urgency FROM problem_nodes WHERE node_name = "urgency" )t_urgency ON t_urgency.t_urgency_id_problem = main.id_problem INNER JOIN ( SELECT id_problem as t_name_id_problem, node_value AS name FROM problem_nodes WHERE node_name = "name" )t_name ON t_name.t_name_id_problem = main.id_problem INNER JOIN ( SELECT id_problem as t_phone_id_problem, node_value AS phone FROM problem_nodes WHERE node_name = "phone" )t_phone ON t_phone.t_phone_id_problem = main.id_problem INNER JOIN ( SELECT id_problem as t_location_id_problem, node_value AS location FROM problem_nodes WHERE node_name = "location" )t_location ON t_location.t_location_id_problem = main.id_problem INNER JOIN ( SELECT id_problem as t_department_id_problem, node_value AS department FROM problem_nodes WHERE node_name = "department" )t_department ON t_department.t_department_id_problem = main.id_problem INNER JOIN ( SELECT id_problem as t_cc_id_problem, node_value AS cc FROM problem_nodes WHERE node_name = "cc" )t_cc ON t_cc.t_cc_id_problem = main.id_problem INNER JOIN ( SELECT id_problem as t_status_id_problem, node_value AS status FROM problem_nodes WHERE node_name = "status" )t_status ON t_status.t_status_id_problem = main.id_problem INNER JOIN ( SELECT id_problem as t_case_manager_id_problem, node_value AS case_manager FROM problem_nodes WHERE node_name = "case_manager" )t_case_manager ON t_case_manager.t_case_manager_id_problem = main.id_problem INNER JOIN ( SELECT id_problem as t_ip_id_problem, node_value AS ip FROM problem_nodes WHERE node_name = "ip" )t_ip ON t_ip.t_ip_id_problem = main.id_problem INNER JOIN ( SELECT id_problem as t_case_manager_eid_id_problem, node_value AS case_manager_eid FROM problem_nodes WHERE node_name = "case_manager_eid" )t_case_manager_eid ON t_case_manager_eid.t_case_manager_eid_id_problem = main.id_problem)t This pattern of retrieving data is a bit foreign to me, so any help would be appreciated.Justin

[justsomeguy]

Is it a requirement that the query return the results in that format, or is it possible to get the results in a different format and then use another language to format the results?

[justinbriggs1]

That's a good question. I don't see any reason why I couldn't get the results in a different format and then modify the data as needed with PHP. That might actually be optimal since I'd rather not rewrite any major data structures. I am still, however, at a loss for ideas at this time. Do you have any suggestions? And also, have you seen anything like this before? I'm not sure if the last developer was doing some "experimenting", but this is not really the way I would normally do things.

[justinbriggs1]

OK, here is one answer. I take no credit for figuring this out by the way:SELECT main.*, MAX(CASE WHEN node_name = 'urgency' THEN node_value END) AS urgency, MAX(CASE WHEN node_name = 'name' THEN node_value END) AS name, MAX(CASE WHEN node_name = 'phone' THEN node_value END) AS phone, MAX(CASE WHEN node_name = 'location' THEN node_value END) AS location, MAX(CASE WHEN node_name = 'department' THEN node_value END) AS department, MAX(CASE WHEN node_name = 'cc' THEN node_value END) AS cc, MAX(CASE WHEN node_name = 'status' THEN node_value END) AS status, MAX(CASE WHEN node_name = 'case_manager' THEN node_value END) AS case_manager, MAX(CASE WHEN node_name = 'ip' THEN node_value END) AS ip, MAX(CASE WHEN node_name = 'case_manager_eid' THEN node_value END) AS case_manager_eid FROM problems AS main INNER JOIN problem_nodes pn ON pn.id_problem = main.id_problem WHERE node_name IN ('urgency','name','phone','location','department','cc','status','case_manager','ip','case_manager_eid') GROUP BY main.id_problem HAVING id_problem_type = '1' AND status != 'complete' AND status NOT LIKE '%project%' ORDER BY date_created DESC LIMIT 20 The query time went from 3.7 seconds to about .1 seconds.