Search code examples
mysqljoininner-joinsql-tuning

joining multiple complicated tables on mysql

I am trying to find all customers who answered 'facebook' on the question "platform" (id 1) enter image description here

each customers have questionnaires and I am trying to look for customers who answered "facebook" on questions "platform" the id for questions are fixed for example "platform" id in question table is 1 , "age" is 2 and etc. Each questionnaire have different set of questions like for example questionnaire id 1 has the question "platform" but the answer will be inputted via selectbox not textbox (thus having the need for answer_option table) and questionnaire id 2 might not have the question platform and etc. The parameters that will be coming from the search screen is the questionnaire id and the text that answers the question "platform".

right now this is what I have so far..

SELECT customers.* FROM customers
INNER JOIN questionnaire_answers
ON questionnaire_answers.answerable_id = customers.id
AND questionnaire_answers.answerable_type = 'Customer'
INNER JOIN questionnaires
ON questionnaires.id = questionnaire_answers.questionnaire_id
INNER JOIN questions
ON questions.questionnaire_id = questionnaires.id
INNER JOIN answers
ON answers.question_id = questions.id
INNER JOIN answer_options
ON answer_options.id = answers.answer_option_id
WHERE (questions.id = 1 and answers.answer LIKE '%facebook%');

if you notice the question.id will be always 1 because I am just looking for the answers in platform , it is a special function of my screen. I tried it with 1 - 20 row datas it works but if the data gets bigger like 20,000 rows it never returns the record I already tried with the real data the query takes forever that I needed to cancel it. Since the question id is already constant and the questionnaire id is coming from the search parameter I figure maybe I don't need to join unnecessary table and I don't think placing index will help because I have a wildcard parameter?(not sure), but I can't figure how.

example data and expected result

Customer:
 id:1,
 family_name: "smith",
 first_name: "smith",
..
Customer:
 id:2,
 family_name: "david",
 first_name: "david",
..

QuestionnaireAnswer:
  id: 20199,
  questionnaire_id: 4,
  answerable_type: "Customer",
  answerable_id: "1",
QuestionnaireAnswer:
  id: 20200,
  questionnaire_id: 5,
  answerable_type: "Customer",
  answerable_id: "2",

Questionnaire:
  id: 4,
  name: "Survey for Psychology Students",
  group_code: "10",
Questionnaire:
  id: 5,
  name: "Survey for Students",
  group_code: "10",

Question:
  id: 1,
  name: "platform",
  answer_type: "08", //selectbox
  default_text: nil,
  sequence: 1,
  question_valid: true,
  disabled: false,
  questionnaire_id: 5,
  ...
Question:
  id: 2,
  name: "school",
  answer_type: "01", //textbox
  default_text: nil,
  sequence: 2,
  question_valid: true,
  disabled: false,
  questionnaire_id: 5,
  ...

Question:
  id: 3, // I said the the id for platform is fixed but this time this question is textbox inputted so it is different
  name: "platform",
  answer_type: "01", //text
  default_text: nil,
  sequence: 1,
  question_valid: true,
  disabled: false,
  questionnaire_id: 5,
  ...
Question:
  id: 4,
  name: "town",
  answer_type: "01", //textbox
  default_text: nil,
  sequence: 2,
  question_valid: true,
  disabled: false,
  questionnaire_id: 4,
  ...

Answer:
  id: 1,
  question_id: 3,
  answer_option_id: nil,
  answer: "facebook",
  questionnaire_answer_id: 19585,
  created_at: Wed, 13 Feb 2019 21:43:15 JST +09:00,
  updated_at: Wed, 13 Feb 2019 21:43:15 JST +09:00,
  member_id: nil>,

Any help will be appreciated.

Solution

  • You're joining with several unnecessary tables. You don't use anything from answer_options, so you don't need that. And answers has foreign keys to questionnaire_answers and questions, so you don't need questionnaires.

    SELECT customers.* 
FROM customers AS c
INNER JOIN questionnaire_answers AS qa
ON qa.answerable_id = c.id
INNER JOIN answers AS a
ON a.questionnaire_answers_id = qa.id
INNER JOIN questions AS q
ON q.id = a.question_id
WHERE q.id = 1
    AND a.answer LIKE '%facebook%'
    AND qa.answerable_type = 'Customer'