How in SQL search and get count for huge table (where-in)

Question

I have sql table:

id  user    city
1   Alisa   New York
2   Alisa   Sydney
3   Alisa   Rom
4   Alisa   Toronto
5   Bob     Rom
6   Bob     Moskow
7   Bob     Sydney
8   Tom     Sydney

And I need get how many same cities have other users for Bob for example.

SELECT user, count(DISTINCT city) FROM table WHERE city IN (
   SELECT city FROM table WHERE user = 'Bob'
) AND user != 'Bob' GROUP BY user

and result is:

user    count(DISTINCT city)
Alisa   2
Tom     1

Do you know other best way to make this request? Is it ok for huge table?

Answer 1

CREATE TEMPORARY TABLE Cities (city varchar(100)); 
INSERT INTO Cities SELECT city FROM table WHERE user = 'Bob';

SELECT user, count(DISTINCT t.city) 
FROM table AS t
INNER JOIN Cities AS c ON t.City=c.City
AND t.user !='Bob'
GROUP BY User

You could create a temporary table, in which you will store the cities, in which Bob was and then do an inner join between your table and the temporary table you created.

Answer 2

You can do this with a self join or with EXISTS, both of which would be far superior to trying to use IN (SELECT ...) which is never a good idea. Whether this is "ok for a huge table" depends on your definition of "ok" and "huge table", along with your schema, but this should at least help point you in the right direction.

Here's the EXISTS version:

SELECT user, count(DISTINCT city) 
FROM table as main_query
WHERE user != 'Bob' 
AND EXISTS (
  SELECT NULL 
  FROM table as sub_query 
  WHERE sub_query.user = 'Bob' 
  and sub_query.city = main_query.city
)
GROUP BY user

Answer 3

How about this:

SELECT A.user, 
       count(DISTINCT A.city)
FROM table  A,
     (SELECT city FROM table WHERE user = 'Bob') B
WHERE A.city = B.city
AND A.user != 'Bob'
GROUP BY A.user

Answer 4

You should pump in some data, then do an EXPLAIN

That is the only way to predict with reasonable accuracy if it is 'OK'.