Greetings to the Community, I need some assistance with a MySQL query that is running very slow. I must admit that I am not a MySQL expert and I wrote this query with hints from several postings on Daniweb, so I'm looking for some hints to speed it up.
Here's the query:
SELECT app_questions.quest_id, app_questions.app_question, app_questions.quest_type, app_questions.box_type, app_questions.quest_exception, MAX( CASE WHEN app_answers.chap_id = '357277' AND app_answers.current_yr = '2012' THEN app_answers.quest_answer END ) AS '2012', MAX( CASE WHEN app_answers.chap_id = '357277' AND app_answers.current_yr = '2011' THEN app_answers.quest_answer END ) AS '2011', MAX( CASE WHEN app_answers.chap_id = '357277' AND app_answers.current_yr = '2010' THEN app_answers.quest_answer END ) AS '2010' FROM app_answers RIGHT JOIN app_questions ON app_answers.quest_id = app_questions.quest_id WHERE app_questions.step_desig='1' GROUP BY app_questions.sector_id, app_questions.quest_id, app_questions.quest_type
The data is arranged in two tables--one for questions with some switches to handle exceptions as the PHP builds the page dynamically, the other for the answers with field to tag the submitter and a timestamp to distinguish when it was submitted.
Table 1 Questions
Fields: Question ID | Question
Table 2 Answers
Fields: Chapter ID | Answer | Current Year
The above query returns a 3 year history of that submitter's answers to a set of questions determined by the field "step_desig". The field "step_desig" denotes the page on which the question is asked and the questions are grouped by subject. Example: All questions on scholastic performance are presented on the same page.
Example: Questions, Answer for 2012, Answer for 2011, Answer for 2010
The application inserts an answer for the question, ONLY if the question has been answered, otherwise the query above returns NULL, so that there is a return for every question whether it has been answered or not.
A few other bits of info for the sake of clarity:
--The fields "quest_type" and "sector_id" denote who answers the question and sector on the page, respectively.
--There are 271 questions with 241 submitters for a total of 119,000 answers currently in the database. Not sure if this is relevant, but I give this information to provide an idea of the current size of the table being queried.
This query works EXACTLY as it should, but unfortunately, the time anaylyser in PHPMyAdmin tell me it takes about 14 seconds to run and the primary complaint about functionality last year was in the time it takes for the page to load. Any advice on speeding it up would be greatly appreciated.