How to do this MySQL two table query best? (speed/performance)

[kellerkind]

Hi,

I have to query the db in the following scenario.

 

I develop a little vocabulary learning application and have two tables. One with users and ids of words that they already know. The other table has all words in the db.

 

I'm looking for a way to get a random word from the table WORDS for steve that is not one of the words he already knows.

 

TABLE USERS

 

user | id

steve | 12

steve | 14

steve | 10

lara | 10

 

TABLE WORDS

 

word | id

fun | 7

hard | 12

cool | 10

strong | 15

 

It would be fantastic if anyone could help me with this one. Right now I first get all the words that Steve already knows and build a query ($protect). Then I use this query to get the random word.

 

"SELECT * FROM vocdb WHERE item = 'Vocabulary' AND status = 'Approved' $protect ORDER BY RAND() LIMIT 1"

 

[fenway]

You need a LEFT JOIN... IS NULL.

[kellerkind]

Found a solution:

 

SELECT word
  FROM words
WHERE id NOT IN
       ( SELECT id 
           FROM users
          WHERE user = 'Steve' )
ORDER
    BY RAND() LIMIT 1

 

But this still works with ORDER BY RAND and I can't find a way to use the alternative posted in the sticky here in this forum. Any ideas?