0

I am making a simple application for survey for clients (lets say students), storing the answers in answer table with the following structure:

CREATE TABLE answers
(
    id INT(11) PRIMARY KEY NOT NULL,
    answer VARCHAR(255),
    studentId INT(11),
    questionIdentifier VARCHAR(255)
);

The answers will be stored like this:

INSERT INTO answers (answer ,studentId, questionIdentifier ) values ('5',222,'math')
INSERT INTO answers (answer ,studentId, questionIdentifier ) values ('4',222,'subject1')
INSERT INTO answers (answer ,studentId, questionIdentifier ) values ('1',222,'subject2')
INSERT INTO answers (answer ,studentId, questionIdentifier ) values ('5',222,'subject3')
INSERT INTO answers (answer ,studentId, questionIdentifier ) values ('3',222,'subject4')

I created view to get those answers as a columns so I return them back as JSOn to front end, shows the answers as columns, and students as rows:

CREATE OR REPLACE VIEW answers_view AS
  SELECT
    s.id AS `studentId`,
    s.number AS `studentNumber`,
    s.name AS `studentName`,
    ad.streetAddress AS `address`,

    ait1.answer AS `math`,ait2.answer AS `subject2`,ait3.answer AS `subject3`,
    ait4.answer AS `subject4`,ait5.answer AS `subject5`,ait6.answer AS `subject6`,

  FROM students s

    LEFT JOIN answers ait1 ON ait1.studentId = s.id AND ait1.questionIdentifier = 'math'
    LEFT JOIN answers ait2 ON ait2.studentId = s.id AND ait2.questionIdentifier = 'subject2'
    LEFT JOIN answers ait3 ON ait3.studentId = s.id AND ait3.questionIdentifier = 'subject3'
    LEFT JOIN answers ait4 ON ait4.studentId = s.id AND ait4.questionIdentifier = 'subject4'
    LEFT JOIN answers ait5 ON ait5.studentId = s.id AND ait5.questionIdentifier = 'subject5'
    LEFT JOIN answers ait6 ON ait6.studentId = s.id AND ait6.questionIdentifier = 'subject6'    
    LEFT JOIN addresses ad ON ad.id = s.id   
  GROUP BY s.id;

This just a sample of my code. I have like 50 questions, each question has its own questionIdentifier.

The view above is working so good but so slow too and the performance is so poor. I have like 6 students but the view lines is big and takes a minutes to finish.

How I can fix this view?

I can smell its bad smell, the code is clear but so bad for performance, and I am not that experienced in SQL.

I appreciate the help for his.

1
  • Is the query slow when you use it separately outside the view? Use EXPLAIN to get the query plan and add it to your question (together with the actual query you use on the view and preferably with the create table for the other tables).
    – jkavalik
    Feb 18, 2016 at 17:23

1 Answer 1

1

Get rid of id in answers. Instead, have PRIMARY KEY(student_id, question_id).

While you are at it, change the 255 down to something realistic.

The sluggishness is due to having to scan the entire answers table for each JOIN.

And I suspect this was just plain wrong:

LEFT JOIN addresses ad ON ad.id = s.id 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.