SELECT IN for a large set

Question

In my web application, I want to find out which of a user's friends on Twitter are already existing on the system... Currently what I am doing is getting the list of Twitter IDs the user is following (Twitter API returns the IDs 5000 at a time), and doing:

SELECT userId FROM users WHERE userId IN (COMMA_SEPARATED_LIST_OF_IDs);

I don't feel comfortable about this query, because as the users table grows, this might prove to be a bottle neck. I don't want to optimize prematurely either, so is there any other way I should be doing this?

Update: I am using MySQL.

Answer 1

You could create a new table, and begin storing all of the twitter id's that your users are following. Then, determining who is already in your system would be a simple join on indexed columns. You can use the Twitter API to load and update that table at your discretion.

Answer 2

You can use the EXISTS function if this is Transact SQL. I'm not sure if EXISTS works in other databases because I only work in SQL Server.

http://msdn.microsoft.com/en-us/library/ms188336.aspx

Answer 3

Two approaches:

1. SELECT IN (expr) is able to have a SELECT expression for expr. I.e. the database can handle a large amount of data here.

2. Use a join.

Answer 4

I'm assuming that users.userId is your primary key. If so, it will already be indexed, so the lookup should already be efficient. Do you expect that your COMMA_SEPARATED_LIST_OF_IDS will grow beyond reason?