[SOLVED] Select random record from mysql with multiple filters

Issue

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.

Solution

Instead of using where id not in, I would use a LEFT JOIN:

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)

Leave a Reply

Your email address will not be published. Required fields are marked *