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.
Best Answer
Get rid of
id
inanswers
. Instead, havePRIMARY 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 eachJOIN
.And I suspect this was just plain wrong: