Filter out rows by hardcoded list in MySQL performance

Question

Hello,

I have a hardcoded list of values like: 1,5,7,8 and so on.

And I must filter out rows from table that have ID in list above, so I do something like this:

Select
* 
from myTable m
   left join othertable t
   on t.REF_ID = m.ID
where m.ID not in (1,5,7,8...)

But when I have more values (like 1000) and more rows (100) in othertable and myTable this query starts to be slow. I have an index on REF_ID and ID. It seems that the part "where m.ID in (1,5,7,8) is the problem.

Is there faster way to filter out rows by hardcoded list of values?

Answer 1

Try putting your list in a temporary table as temptable.ID and doing

SELECT * 
FROM myTable m
LEFT JOIN othertable t ON t.REF_ID = m.ID
LEFT JOIN temptable ON m.ID = temptable.ID
WHERE temptable.ID IS NULL