MySQL Forums
Forum List  »  Newbie

Re: EXPLAIN shows optimized query
Posted by: Mahesh Kumar
Date: November 18, 2017 12:18AM

SELECT `questions`.`question_id`, `question_multilingual`.`quiz_questions`, `questions`.`FK_quiz_id`, GROUP_CONCAT(choices.choice_id ORDER By choices.choice_id ASC) as choice_id_array, GROUP_CONCAT(choice_multilingual.choice ORDER By choice_multilingual.FK_choice_id ASC) as choice_array, `question_multilingual`.`FK_language_id` FROM `questions` INNER JOIN `question_multilingual` ON `questions`.`question_id` = `question_multilingual`.`FK_question_id` INNER JOIN `choices` ON `choices`.`FK_question_id` = question_multilingual.FK_question_id INNER JOIN `choice_multilingual` ON `choice_multilingual`.`FK_choice_id` = `choices`.`choice_id` WHERE `questions`.`FK_quiz_id` = '1' GROUP BY `questions`.`question_id` ORDER BY RAND()

If i dont use language id, 

1	SIMPLE	question_multilingual	NULL	ALL	FK_question_id	NULL	NULL	NULL	407	100.00	Using temporary; Using filesort	
1	SIMPLE	questions	NULL	eq_ref	PRIMARY,FK_quiz_id	PRIMARY	4	quizzes.question_multilingual.FK_question_id	1	68.60	Using where	
1	SIMPLE	choices	NULL	ref	PRIMARY,FK_question_id	FK_question_id	4	quizzes.question_multilingual.FK_question_id	5	100.00	Using index	
1	SIMPLE	choice_multilingual	NULL	ref	FK_choice_id	FK_choice_id	4	quizzes.choices.choice_id	6	100.00	NULL

1. It is still not providing 100% optimized results
2. I am still getting only 59 results. All of them are in english only. So i am not able to filter the question by language. Choices are completely mixed.

Can we get each language question in different column and each language choices in different column? Please let me know how to write the query


Options: ReplyQuote

Written By
October 31, 2017 03:11AM
Re: EXPLAIN shows optimized query
November 18, 2017 12:18AM

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.