Optimize database query

Question

I have the following database structure.

question table.

id  question               desc
1   What was john's age?   About john

option table

id  question_id  option_value    correct_ans
1      1             20              0
2      1             18              0
3      1             28              1
4      1             60              0

Now i want to get the record from both the tables with all questions with their options. I want the following structure. I did the code and get the result but it took two sql queries. first to get all the questions from question table then create an array and then get the options for all questions and put in the array. Is it possible to get such type of data using a single query?

      array([0]=array('question_id'=>1,
                      'question'=>'what was john's age',
                      'desc'=>'About john',
                      'options'=>array([0]=>array('option_id'=>'1',
                                                  'option_value'=>20,
                                                  'correct_ans'=>0),
                                       [1]=>array('option_id'=>'2',
                                                  'option_value'=>18,
                                                  'correct_ans'=>0),
                                       [2]=>array('option_id'=>'3',
                                                  'option_value'=>28,
                                                  'correct_ans'=>1),
                                       [3]=>array('option_id'=>'4',
                                                  'option_value'=>60,
                                                  'correct_ans'=>0)
                                       )
                       ),
             [1]=array('question_id'=>2,
                        etc...............

Why do you want to have a single query? 2 queries is a perfect solution for a given problem. Saying "2 queries is slower than one" is actually the same as "1 $2 note is better than 2 $100s" — zerkms
– zerkms, Commented Aug 3, 2013 at 6:55

Barmar · Accepted Answer · 2013-08-03 07:09:23Z

1

Use a join:

SELECT q.id question_id, question, desc, o.id option_id, option_value, correct_ans
FROM question q
LEFT JOIN option o
ON q.id = o.question_id
ORDER BY question_id, option_id

This uses a LEFT JOIN so you'll get information about the question even if no options appear in the options table (the columns from the options table will be NULL in this case). If that's not allowed, you can use an INNER JOIN instead.

The results of this query will repeat the question information for each row. Your loop that creates the array from the results will have to check whether the question_id has changed from the previous row. If it has, it starts a new element of the top-level array; if the question_id is the same, it adds to the options element of the current array element.

answered Aug 3, 2013 at 7:09

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

zerkms Over a year ago

The original question was about "optimization". In this case the solution with LEFT JOIN would barely be more efficient than 2 queries

Barmar Over a year ago

Is it really just 2 queries? The usual alternative to a join is to do one query to get all the question IDs, and then a query per question to get all the options. So if there are N questions, it's N+1 total queries. I know you could generate a big WHERE question_id IN (...) clause from the first query, but that's not what people usually do. Anyway, he asked if there's a way to get everything using a single query, and this is it.

zerkms Over a year ago

"but that's not what people usually do" --- OP said it takes 2 queries for him. And as soon as the question is entitled as "optimization" - I bet that a solution with one query would consume more resources (network + RAM)

Barmar Over a year ago

Even if it's not a big performance difference, a join is the natural way to do this. Any competent database programmer needs to learn how to write joins.

Collectives™ on Stack Overflow

Optimize database query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related