MySQL Forums
Forum List  »  Newbie

Re: EXPLAIN shows optimized query
Posted by: Mahesh Kumar
Date: November 01, 2017 02:42PM

explain SELECT * FROM `questions` INNER JOIN `question_multilingual` ON `questions`.`question_id` = `question_multilingual`.`FK_question_id` INNER JOIN `choices` ON `questions`.`question_id` = `choices`.`FK_question_id` INNER JOIN `choice_multilingual` ON `choice_multilingual`.`FK_choice_id` = `choices`.`choice_id` WHERE `questions`.`FK_quiz_id` = '1' AND `choice_multilingual`.`FK_language_id` = 2 AND `question_multilingual`.`FK_language_id` = 2 GROUP BY `questions`.`question_id` ORDER BY RAND();

+----+-------------+-----------------------+------------+--------+------------------------+----------------+---------+----------------------------------------------+------+----------+----------------------------------------------+
| id | select_type | table                 | partitions | type   | possible_keys          | key            | key_len | ref                                          | rows | filtered | Extra                                        |
+----+-------------+-----------------------+------------+--------+------------------------+----------------+---------+----------------------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | question_multilingual | NULL       | ALL    | FK_question_id         | NULL           | NULL    | NULL                                         |   84 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | questions             | NULL       | eq_ref | PRIMARY,FK_quiz_id     | PRIMARY        | 4       | quizzes.question_multilingual.FK_question_id |    1 |   100.00 | Using where                                  |
|  1 | SIMPLE      | choices               | NULL       | ref    | PRIMARY,FK_question_id | FK_question_id | 4       | quizzes.question_multilingual.FK_question_id |    6 |   100.00 | NULL                                         |
|  1 | SIMPLE      | choice_multilingual   | NULL       | eq_ref | FK_choice_id           | FK_choice_id   | 6       | quizzes.choices.choice_id,const              |    1 |   100.00 | NULL                                         |
+----+-------------+-----------------------+------------+--------+------------------------+----------------+---------+----------------------------------------------+-

It produces 84 results.

But if i remove the condition of FK_language_id

mysql> explain SELECT * FROM `questions` INNER JOIN `question_multilingual` ON `questions`.`question_id` = `question_multilingual`.`FK_question_id` INNER JOIN `choices` ON `questions`.`question_id` = `choices`.`FK_question_id` INNER JOIN `choice_multilingual` ON `choice_multilingual`.`FK_choice_id` = `choices`.`choice_id` WHERE `questions`.`FK_quiz_id` = '1' AND `choice_multilingual`.`FK_language_id` = 1 GROUP BY `questions`.`question_id` ORDER BY RAND();
+----+-------------+-----------------------+------------+--------+------------------------+----------------+---------+------------------------------------------+------+----------+----------------------------------------------+
| id | select_type | table                 | partitions | type   | possible_keys          | key            | key_len | ref                                      | rows | filtered | Extra                                        |
+----+-------------+-----------------------+------------+--------+------------------------+----------------+---------+------------------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | choice_multilingual   | NULL       | ALL    | FK_choice_id           | NULL           | NULL    | NULL                                     |  306 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | choices               | NULL       | eq_ref | PRIMARY,FK_question_id | PRIMARY        | 4       | quizzes.choice_multilingual.FK_choice_id |    1 |   100.00 | NULL                                         |
|  1 | SIMPLE      | questions             | NULL       | eq_ref | PRIMARY,FK_quiz_id     | PRIMARY        | 4       | quizzes.choices.FK_question_id           |    1 |   100.00 | Using where                                  |
|  1 | SIMPLE      | question_multilingual | NULL       | ref    | FK_question_id         | FK_question_id | 4       | quizzes.choices.FK_question_id           |    2 |   100.00 | NULL                                         |

Now it produces 306 results.

if it is 306 results, i have to filter the data in PHP. But, though it is 306 results, explain looks same for me

Options: ReplyQuote


Subject
Written By
Posted
October 31, 2017 03:11AM
Re: EXPLAIN shows optimized query
November 01, 2017 02:42PM


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.