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).
Actual SQL:
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
NOT IN
toLEFT JOIN
. 2) ReplaceDATE(added) = CURDATE()
withadded >= CURDATE() AND added < CURDATE() + INTERVAL 1 DAY
3)How many records (approximately) does the subqueries selects? – Akina May 6 '19 at 11:55