I have a table with 800k rows and I need to select 20 random rows that not are in other 2 tables (tickets already purchased).
I already tried a SQL with NOT IN syntax, but it's still (sometimes 1s in development machine).
SELECT id, description FROM ticket WHERE id NOT IN ( SELECT id FROM cart_item WHERE DATE(added) = CURDATE() ) AND id NOT IN ( SELECT id FROM valid_ticket ) AND id BETWEEN 1 AND 200000 ORDER BY RAND() LIMIT 20
I need some faster. Because grabbing all table and select 20 rand rows is very slow. That function is executed is all pages of a website and I'm thinking how to optimize and cache that query.
Thanks all for support