MySQL huge IN is set for a huge table

Question

Consider this theoretical question so practical.

One has a table with, say, 1,000,000+ user entries, and you need to extract data, say, 50,000 from this table, using only user_id. How would you expect IN to behave? If this is not good, is this the only option or is there anything else you can try?

Answer 1

The IN functionality has a rather poor performance, so I would have avoided this. You can get most of the time using a federated query, so depending on the structure of your database, you must finally approve joining the IN-statement.

Answer 2

You can insert your search values ​​into one temporary column table and join it. I have seen other databases do Bad Things when very large offers are presented.

Answer 3

If IN starts proving a nuisance (as other responders suggested, you can try rewriting your query using EXISTS instead.

SELECT * FROM MYTAB WHERE MYKEY IN (SELECT KEYVAL FROM MYOTHERTAB WHERE some condition) 

can be

 SELECT * FROM MYTAB WHERE EXISTS (SELECT * FROM MYOTHERTAB WHERE some condition AND MYTAB.MYKEY = MYOTHERTAB.KEYVAL) 

I often find that speed accelerates quite a bit.

Answer 4

Use JOIN to select the data you need.