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