Speed-up/Optimise MySQL statement – finding a new row that hasn’t been selected before

First a bit of background about the tables & DB.

I have a MySQL db with a few tables in:

films: Contains all film/series info with netflixid as a unique primary key.

users: Contains user info “ratingid” is a unique primary key

rating: Contains ALL user rating info, netflixid and a unique primary key of a compound “netflixid-userid”

This statement works:

SELECT * 
FROM films 
WHERE 
    INSTR(countrylist, 'GB') 
    AND films.netflixid NOT IN (SELECT netflixid FROM rating WHERE rating.userid = 1) 
LIMIT 1

but it takes longer and longer to retrieve a new film record that you haven’t rated. (currently at 6.8 seconds for around 2400 user ratings on an 8000 row film table)

First I thought it was the INSTR(countrylist, ‘GB’), so I split them out into their own tinyint columns – made no difference. I have tried NOT EXISTS as well, but the times are similar.

Any thoughts/ideas on how to select a new “unrated” row from films quickly?

Thanks!

Answer

Try just joining?

SELECT * 
FROM films
LEFT JOIN rating on rating.ratingid=CONCAT(films.netflixid,'-',1)
WHERE 
    INSTR(countrylist, 'GB')
    AND rating.pk IS NULL
LIMIT 1

Or doing the equivalent NOT EXISTS.