Q/A trivia quiz query This project is based on trivia (question/answer quiz). The scenario is simple but I have to improve/optimize my query using these tables:



$query=$this-&gt;db-&gt;query("SELECT
q.`id`,
q.`topic_id`,
t.`topic`,
q.`question`,
GROUP_CONCAT(a.`answer`) AS `answer`,
GROUP_CONCAT(a.`is_correct`) AS `is_correct`,
GROUP_CONCAT(a.`id`) AS `ans_id`,
q.`answer_type`,
q.`image_url`,
q.`created_date`,
(SELECT ua.`topic_id` FROM usersAnswer ua 
WHERE t.`id`= ua.topic_id AND ua.`user_id` = '".$user_id."' LIMIT 1) 
AS `userdone_topic`,
(SELECT ua.`answer_id` 
FROM usersAnswer ua 
WHERE ua.`question_id` = q.`id` AND ua.`user_id`='".$user_id."' 
AND ua.`answer_id` 
IN
(SELECT a.`id` FROM answers a
WHERE a.`is_correct` != '0' AND a.question_id = q.`id`
)) AS `user_correct_id`
FROM
questions q INNER JOIN topics t ON q.`topic_id`= t.`id`
INNER JOIN answers a ON q.`id`=a.`question_id`  
WHERE 
(SELECT s.`status` 
FROM `status` s 
WHERE 
s.`month_date`= q.created_date AND s.`status`= 2
$valid)
GROUP BY q.`id`
");
return $query-&gt;result();
well I used the MS sql for reference but that should be a bit batter. I would also reccomend you to structure your sql cos that way you get a better overview of things.
Carefull the below seelect is not grouped you can group it as a subquery again

SELECT q.id, q.topic_id, q.question, a.answer, a.is_correct, a.id as ans_id,
    q.answer_type, q.image_url, q.created_date, uaAll.topic_id as userdone_topic,
    uaCorrect.answer_id as user_correct_id
FROM questions q
    inner join topics t on q.topic_id = t.id
    inner join answers a on q.id = a.question_id
    inner join status s on q.created_date = s.month_date
    left outer join 
    (
        SELECT top 1 ua.topic_id
        FROM UserAnswers ua
        where ua.userid = ''
    ) as uaAll on t.id = uaAll.topic_id
    left outer join (
        select ua.answer_id,ua.question_id
        FROM UserAnswers ua
            inner join answers a on a.id = ua.answer_id
        where ua.userid = ''  
            and a.is_correct &lt;&gt; 0
    ) as uaCorrect on q.id = uaCorrect.question_id AND a.id = uaCorrect.answer_id
WHERE s.status = 2