In MySQL, should I add an index even if the query that scans the table is only ran once a month?
An IN clause breaks down to an OR

SELECT type FROM users WHERE type = 1 OR type = 3;


OR is typically (but not always) non-SARGable. That means an index won't be used too seek data but will be scanned. This is the same (roughly) as scanning the table anyway: same number of rows.

So given there is minimal benefit, I wouldn't bother in most cases.

If you have millions of rows and the query is blocking other processes, then you can do this if you have an index. No you have 2 seeks.

SELECT type FROM users WHERE type = 1
UNION ALL
SELECT type FROM users WHERE type = 3;


Summary: not worth it unless you have a big table and blocking issues.