SQL query taking too much time to exectute

Question

I have two tables. Table 1 (for questions) has 2 columns "qid" (the question id) and "question". Table 2 (for answers) has "aid" (answer id), "qid" and "answers". The "qid" in table 2 is a foreign key from table 1 and a single question may have multiple answers.

Both tables have 30,000+ entries.

I have made a loop,

for(each qid)
{
select question from table1 where qid = id;
select answer from table2 where qid = id;
}

The number of ids passed is 10 (to the loop). The query takes about 18 seconds to execute. Is this normal for this much delay or is there something wrong with this approach. I want to know if there is any way to make the above query faster.

Answer 1

You can do it in a single query which should be a lot faster.

SELECT t1.qid, t1.question, t2.aid, t2.answer
FROM table1 t1
INNER JOIN table2 t2 ON t2.qid = t1.qid
WHERE t1.qid IN (?,?,?,etc.);

Answer 2

Or you can do

SELECT t1.qid, t1.question, t2.aid, t2.answer FROM table1 t1, table2 t2 WHERE t1.qid=t2.qid AND t1.qid IN(...some condition...);

Answer 3

I completely agree with @wvdz.

Additionally this is a general list of things you can do to improve performance of selects:

1. Analyze & possibly rewrite your query, (there's a lot left unsaid here so I recommend visiting one of the resource links I've included, or both).
2. If the query includes what is effectively the primary key, make sure you have actually created the primary key constraint for that column (this creates an index)
3. Consider creating indexes for any columns that will be used in the conditions of the query (similar to point one, you will want to read up on this if you think you need more optimization, this becomes more important the more data you have in a table)

Also here are a couple of good resources for tuning your sql queries: http://beginner-sql-tutorial.com/sql-query-tuning.htm http://www.quest.com/whitepapers/10_SQL_Tips.pdf

NOTE on Primary Keys: Should you want more information on the use of Primary Keys this answer I gave in the past explains how I use primary keys and why... mentioning this because, in my opinion & experience, every table should include a primary key: MySQL - Should every table contain it's own id/primary column?