I know this was discussed many times but my research did not help me with my problem.
I have a table (innodb) with about 3k records. I need to pick 1 row random with some filters, which i do it like this:
select id, title, topic_id from posts where id not in (select post_id from records where user_id='$my_id' and checked='1') and topic_id='$topic_id' and status='1' order by RAND() limit 1
This gives me the result i wanted. The problem is this takes too much time even with 3k records. It will get slower when records are increased.
I have to find a solution for this. Any suggestions?
Update: Both tables are indexed with id columns.
Instead of using
where id not in, I would use a
SELECT id, title, topic_id FROM posts p LEFT JOIN records r ON p.id = r.post_id AND r.user_id='$my_id' AND r.checked = '1' WHERE p.topic_id='$topic_id' AND status='1' AND r.post_id IS NULL ORDER BY RAND() LIMIT 1;
With this, you will want an index on
posts.id and another index on
records.post_id, records.user_id, records.checked
Answered By – Jacob Lambert
Answer Checked By – Jay B. (BugsFixing Admin)