SQL left join query runs VERY slow

mysql, optimization, sql

Solution

You most likely need an index on

responses.questionID
responses.username

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

Problem

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.

Original source