Alternative to SELECT .. IN (..)

Question

I have table with 3 columns: id (of person), city (which person visited) and date (of his visit). I need to select all people (their ids) which were in city Los Angeles between specific date, who also were in city New York on some other specific date.

Here is an example of the query I run now:

SELECT DISTINCT id 
FROM places 
WHERE date >= '2011-03-18' AND 
      date < '2011-03-19' AND 
      city = 'los angeles' AND 
      id IN (SELECT DISTINCT id 
             FROM places 
             WHERE date >= '2011-03-15' AND 
                   date < '2011-03-16' AND 
                   city = 'chicago' 
             GROUP BY id);

Anything I can also? Is there are any other query I can use? This select is way too slow.

Answer 1

Using a join (with an alias) instead of a subquery should perform better:

  SELECT places.id 
    FROM places 
         JOIN places AS places2 ON places2.id = places.id
     AND places2.date >= '2011-03-15' 
     AND places2.date < '2011-03-16' 
     AND places2.city = 'chicago' 
   WHERE places.date >= '2011-03-18' 
     AND places.date < '2011-03-19' 
     AND places.city = 'los angeles' 
GROUP BY places.id;