Page 1 of 1

How to optimize the following query?

Posted: Wed Feb 09, 2011 4:36 am
by agriz
Hi,

There are three tables involved here.
A kind of quiz localhost site, i did for me.

Table 1 contains questions.
==========================
question_id (autoincrement)
questions (varchar 250)
category_id

Table 2 contains choices
========================
Choice_id (autoincrement)
Choice_value (varchar)
question_id

Table 3 contains answers
========================
question_id
choice_id

I am displaying 10 questions from every category

Code: Select all

select * from questions where cat_id = 3 ORDER BY RAND() LIMIT 10
now i need to write 10 queries to display the choices.

Code: Select all

select * from choice where question_id = xx
What is the better solution to reduce the query?

Re: How to optimize the following query?

Posted: Wed Feb 09, 2011 4:48 am
by Darhazer
make sure you have index on cat_id :)

Fetch the cat_ids in PHP and make the following:

Code: Select all

$sql = 'select * from choice where question_id IN('.implode(',', $cat_ids).')'
In this way you will fetch all choices with single query
Again, make sure you have index on question_id

Re: How to optimize the following query?

Posted: Wed Feb 09, 2011 5:49 am
by agriz
Inside the implode i should actually pass the question id (total 10 question for every category)
But how will i get array of question id?

It should be like this.
First i will get 10 questions in a while loop

Code: Select all

while(question_result = ....) // This while will loop for 10 times
{
question_result->question_value
   //Now i have to write another query to get choices.
   $query = "get choices where question_id = ".question_result->question_id; // 10 queries is going here
   //Another while loop to display choices of the questions which varies from 2 to many
   while(choice_result = ...){ echo "...";}
}
This is a actual thing i have done. Now i feel, i am writing too many query for simple thing.