0

I have a customer table and questionnaire table both have questionnaire_answers , and both questionnaire_answers and question table have answers , I don't know if the problem is in the model design in the first place but the below query return 4,000 rows of customer in like 4 mins. I 'm very beginner when it comes to sql and optimization and have little to no knowledge when it comes to index. Can anyone help me?

in the below query I'm looking for a customers that answered 'Facebook' in the question 'platform'

I am using rails. These are my models

customer class

class Customer < ApplicationRecord

has_many :questionnaire_answers, as: :answerable

end

questionnaire_answer class

class QuestionnaireAnswer < ApplicationRecord

belongs_to :answerable, polymorphic: true
belongs_to :questionnaire
has_many :answers

end

questionnaire class

class Questionnaire < ApplicationRecord

has_many :questionnaire_answers, as: :answerable
has_many :questions

end

question class

class Question < ApplicationRecord

has_many :answers
belongs_to :questionnaire
has_many :answer_options

end

answer_option class

class AnswerOption < ApplicationRecord

has_many :answers
belongs_to :question

end

answer class

class Answer < ApplicationRecord


belongs_to :question
belongs_to :answer_option
belongs_to :questionnaire_answer

end

my sql

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.name = 'platform' and answer_options.answer LIKE '%Facebook%')

mysql EXPLAIN result

    '1','SIMPLE','questionnaire_answers','ALL',NULL,NULL,NULL,NULL,'5','Using where'
'1','SIMPLE','customers','eq_ref','PRIMARY','PRIMARY','8','tech-consul_development.questionnaire_answers.answerable_id','1','Using where'
'1','SIMPLE','questionnaires','eq_ref','PRIMARY','PRIMARY','8','tech-consul_development.questionnaire_answers.questionnaire_id','1','Using where; Using index'
'1','SIMPLE','answers','ALL',NULL,NULL,NULL,NULL,'113','Using where; Using join buffer (Block Nested Loop)'
'1','SIMPLE','questions','eq_ref','PRIMARY','PRIMARY','8','tech-consul_development.answers.question_id','1','Using where'
'1','SIMPLE','answer_options','eq_ref','PRIMARY','PRIMARY','8','tech-consul_development.answers.answer_option_id','1','Using where'
Johji
  • 240
  • 1
  • 19
  • 1
    What the tables structures looks like? hard to optimize without the table definitions – Book Of Zeus Feb 13 '19 at 03:59
  • 1
    sorry about that I will add in a few seconds – Johji Feb 13 '19 at 03:59
  • i Added the models structures – Johji Feb 13 '19 at 04:59
  • 1
    not familiar with ruby, and I dont see any indexes ... – Book Of Zeus Feb 13 '19 at 05:11
  • each table has auto-increment primary keys which are the ids, are you suggesting that I make a column pk beside the id ? – Johji Feb 13 '19 at 05:31
  • yes if you can add index on other columns (specifically the one you are querying in the WHERE and ON - that will improve performance with high amount of data) – Book Of Zeus Feb 13 '19 at 05:33
  • the one querying in the where and on already have PK , base on the result of mysql EXPLAIN , the table questionnaire_answers and answers table doesn't have pk. questionnaire_answers table only have id as a uniq column should I make this my PK ? – Johji Feb 13 '19 at 05:38
  • any column you are querying probably need an index, try it and run the explain agian – Book Of Zeus Feb 13 '19 at 05:41
  • Can you explain why the command EXPLAIN says that tables beside questionnaire_answers and answers has primary key but they are just using ID as PK? – Johji Feb 13 '19 at 05:48
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/188322/discussion-between-book-of-zeus-and-johji). – Book Of Zeus Feb 13 '19 at 05:48

1 Answers1

-1

Let create index for columns after WHERE clause if they are not primary keys. And let try put the tables with little data first of SELECT clause and use explain query to check

Vu Tuan Khac
  • 1