MySQL Forums
Forum List  »  Newbie

EXPLAIN shows optimized query
Posted by: Mahesh Kumar
Date: October 31, 2017 03:11AM

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.

Options: ReplyQuote


Subject
Written By
Posted
EXPLAIN shows optimized query
October 31, 2017 03:11AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.