Optimize IN clause in where query with order by - MySQL
I was looking at a very similar problem today. After doing a ton of searching online, I found this great article by Percona. 

Using "UNION ALL", you can join together a list of queries, like so:

(SELECT * FROM `test` WHERE user_id = 9898 AND status = 1 ORDER BY id LIMIT 30) 
UNION ALL
(SELECT * FROM `test` WHERE user_id = 9898 AND status = 3 ORDER BY id LIMIT 30)
UNION ALL
(SELECT * FROM `test` WHERE user_id = 9898 AND status = 4 ORDER BY id LIMIT 30)
ORDER BY id LIMIT 30


It looks gnarly compared to select ... status IN (1,3,4), but it's effective in avoiding the filesort.

As long as the inner "SELECT..." statements are efficient, the performance is good.