Help with a very slow MySQL query

[visualmonk]

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.

[smantscheff]

To get a serious answer, submit a complete test case with all CREATE TABLE statements and some test data.
Until then, here a first try:

SELECT app_questions.quest_id, app_questions.app_question, app_questions.quest_type, app_questions.box_type, app_questions.quest_exception, 
app_answers.current_yr
max(app_answer.quest_answers),
FROM app_answers RIGHT JOIN app_questions ON app_answers.quest_id = app_questions.quest_id 
WHERE app_questions.step_desig='1' 
and  app_answers.chap_id = '357277'
and current_yr between 2010 and 2012
GROUP BY app_questions.sector_id, app_questions.quest_id, app_questions.quest_type, app_answers.current_yr

You will have to change your display logic with this query as you get one row for each year instead of columns for years, but it should return the same data as the original query.

[visualmonk]

Hello smantscheff. Thanks for your response and solution. As you have noted, the display is the reason that I constructed the original query the way that I did. Outputing the data in columns, with a NULL answer where applicable made displaying it so much easier, but the lag time is turn-off for my users.

[smantscheff]

Build a fast query, read it in rows into a PHP array and traverse the array in columns instead of rows to fit in your display logic.