SQL left join query runs VERY slow

Question

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.

Answer 1

You most likely need an index on

responses.questionID
responses.username 

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

Answer 2

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.