开发者

SELECT IN for a large set

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 gr开发者_如何学Cows, 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.


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.


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.


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?


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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜