0

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

Improve this question
5
  • 1
    1) Rewrite NOT IN to LEFT JOIN. 2) Replace DATE(added) = CURDATE() with added >= CURDATE() AND added < CURDATE() + INTERVAL 1 DAY 3)How many records (approximately) does the subqueries selects? – Akina May 6 '19 at 11:55
  • And, because you use more than one table, add table alias to EACH field in a query. – Akina May 6 '19 at 12:05
  • Hi @Akina, thanks. the num of records it's variable, because it's a table of carts and sales. But we can be talking in something like 10k carts per day and 50k valid tickets (per week - where is missing in this query). – Thiago A. Klein May 6 '19 at 14:04
  • You could add the AND id BETWEEN 1 AND 200000 to both of the subqueries. – raphael75 May 7 '19 at 18:41
  • Guys, the users add items in cart_item and valid_ticket based on the results of this query. So there are only rows between 1 and 200k on that 2 related tables. – Thiago A. Klein May 8 '19 at 1:53
0

First, ORDER BY RAND() will collect all the rows, tack on RAND(), sort, and then start work.

Actually, it is not that bad, since you have filtering. But it may not help a lot.

AND id BETWEEN 1 AND 200000

limits the work to 200K instead of 800K.

The other filters involve what might be a terribly inefficient construct: NOT IN ( SELECT ... ). It may be turned into a different construct. Please provide EXPLAIN SELECT ...

DATE(added) = CURDATE()

Assuming added is DATETIME or TIMESTAMP, and there are not values in the future, then change to

added >= CURDATE()

and have INDEX(added, id).

After I see the EXPLAIN, I may suggest using EXISTS or LEFT JOIN.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.