Create view shows single column values as columns aliases (names) in view 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.
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