MySQL performance issue - lots and lots of ORing

Question

The Facebook API has a call $facebook->api_client->friends_getAppUsers(); and that returns the friend ids of a user who is using the app.

Now say I want to select from my db table all the rows that match all those friend ids.

The only way I can think of doing it would be

SELECT * FROM my_table WHERE uid IN(friend1, friend2, friend3, friend4 ... friend100);

But wouldn't this SELECT by prohibitive, performance wise? Is there another way to achieve the same thing? This should be a pretty common thing in facebook apps.

If you look at the Causes app it allows you to see the Causes of your friends. I'm guessing the app would have to pick out all rows from a Causes table that match all the user ids.

Am I going about this the wrong way?

Answer 1

If you don't have an index on uid, query analyzer will do a table scan on my_table and test each row against the list of friends (think of that as just a cascade of if statements executing on each row). So it's about as costly as:

SELECT * FROM my_table;

Now if you create an index on uid, the query analyzer may still do a table scan if the set of friends being tested against is relatively large. If there are relatively few friends in the list, it can do individual lookups by uid. Run some experiments on realistic data sets and see what happens. You can ask MySQL for any query plan by prefixing an EXPLAIN to the query.