EXPLAIN shows optimized query
CREATE TABLE `questions` (
`question_id` int(5) unsigned NOT NULL AUTO_INCREMENT,
`FK_quiz_id` int(5) unsigned NOT NULL,
`question_identify_text` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`question_id`),
KEY `FK_quiz_id` (`FK_quiz_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `question_multilingual` (
`question_multilingual_id` int(5) unsigned NOT NULL AUTO_INCREMENT,
`FK_question_id` int(5) unsigned NOT NULL,
`FK_language_id` smallint(3) unsigned NOT NULL,
`question_direct` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`question_users` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`question_multilingual_id`),
UNIQUE KEY `FK_question_id` (`FK_question_id`,`FK_language_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `choices` (
`choice_id` int(5) unsigned NOT NULL AUTO_INCREMENT,
`FK_question_id` int(5) unsigned NOT NULL,
`choice_identify_text` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`choice_id`),
KEY `FK_question_id` (`FK_question_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `choice_multilingual` (
`choice_multilingual_id` int(5) unsigned NOT NULL AUTO_INCREMENT,
`FK_choice_id` int(5) unsigned NOT NULL,
`FK_language_id` smallint(3) unsigned NOT NULL,
`choice` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`choice_multilingual_id`),
UNIQUE KEY `FK_choice_id` (`FK_choice_id`,`FK_language_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
EXPLAIN SELECT * FROM `questions` inner join question_multilingual on question_multilingual.FK_question_id = questions.question_id WHERE FK_quiz_id = 1
This explain statement produces
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE questions NULL ref PRIMARY,FK_quiz_id FK_quiz_id 4 const 1 100.00 NULL
1 SIMPLE question_multilingual NULL ALL FK_question_id NULL NULL NULL 2 100.00
Using where; Using join buffer (Block Nested Loop)
EXPLAIN SELECT * FROM `choices` inner join choice_multilingual on choice_multilingual.FK_choice_id = choices.choice_id WHERE FK_question_id = 1
It looks very very bad.
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE choices NULL ref PRIMARY,FK_question_id FK_question_id 4 const 4 100.00 NULL
1 SIMPLE choice_multilingual NULL ALL FK_choice_id NULL NULL NULL 10 20.00
Using where; Using join buffer (Block Nested Loop)
I want to join all the tables in a single query.
EXPLAIN SELECT * FROM `questions` inner join question_multilingual on question_multilingual.FK_question_id = questions.question_id AND question_multilingual.FK_language_id = 1 INNER JOIN choices ON choices.FK_question_id = questions.question_id INNER JOIN choice_multilingual ON choice_multilingual.FK_choice_id = choices.choice_id AND choice_multilingual.FK_language_id = 1 WHERE FK_quiz_id = 1
id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra
1
SIMPLE
questions
NULL
ref
PRIMARY,FK_quiz_id
FK_quiz_id
4
const
1
100.00
NULL
1
SIMPLE
question_multilingual
NULL
eq_ref
FK_question_id
FK_question_id
6
quizzes.questions.question_id,const
1
100.00
NULL
1
SIMPLE
choice_multilingual
NULL
ALL
FK_choice_id
NULL
NULL
NULL
10
10.00
Using where; Using join buffer (Block Nested Loop)
1
SIMPLE
choices
NULL
eq_ref
PRIMARY,FK_question_id
PRIMARY
4
quizzes.choice_multilingual.FK_choice_id
1
20.00
Using where
It produces poor result.
Where do i make the mistake?
Is it query or the design?
Please help. I thought, i designed it beautifully. But disappointed to see the result of explain.