Alex Rivera | Logout

SQL left join query runs VERY slow

Asked 2009-10-15T23:29:21.460
9

Basically I'm trying to pull a random poll question that a user has not yet responded to from a database. This query takes about 10-20 seconds to execute, which is obviously no good! The responses table is about 30K rows and the database also has about 300 questions. 

SELECT  questions.id
FROM  questions
LEFT JOIN  responses ON ( questions.id = responses.questionID
AND responses.username =  'someuser' ) 
WHERE
responses.username IS NULL 
ORDER BY RAND() ASC 
LIMIT 1

PK for questions and reponses tables is 'id' if that matters.

Any advice would be greatly appreciated.

sql mysql optimization
Edit
Report

2 Answers

11

You most likely need an index on

responses.questionID
responses.username

Without the index searching through 30k rows will always be slow.

answered 2009-10-15T23:35:06.183
5

Here's a different approach to the query which might be faster:

SELECT q.id
FROM questions q
WHERE q.id NOT IN (
    SELECT r.questionID
    FROM responses r
    WHERE r.username = 'someuser'
)

Make sure there is an index on r.username and that should be pretty quick.

The above will return all the unanswered questios. To choose the random one, you could go with the inefficient (but easy) ORDER BY RAND() LIMIT 1, or use the method suggested by Tom Leys.

answered 2009-10-15T23:45:21.740

Your Answer