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 >= '20开发者_如何学C11-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.
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;
SELECT id
FROM places AS p1
INNER JOIN AS p2 ON p1.id = p2.id AND p2.date >= '2011-03-15' AND p2.date < '2011-03-19'
AND p2.city = 'chicago'
WHERE p1.date >= '2011-03-18' AND p1.date < '2011-03-19' AND p1.city = 'los angelis'
Try the inner join
SELECT DISTINCT p1.id
FROM places p1
INNER JOIN places p2 ON p1.id = p2.id AND
p2.date >= '2011-03-18' AND
p2.date < '2011-03-19' AND
p2.city = 'los angeles'
WHERE p1.date >= '2011-03-18' AND
p1.date < '2011-03-19' AND
p1.city = 'los angeles' AND
Check the indexes on the table and see if you have any on city and date.
First you need to make sure that you have the relevant indexes in place. In this case it is probably one index with two columns date and city. After that you can take your query and compare the performance with the queries suggested here. Inner join might be faster than what you already got.
My version works in SQL Server. I might work in other DBMS as well.
select id
from places
where [date] >= '2011-03-18' AND
[date] < '2011-03-19' AND
city = 'los angeles'
intersect
select id
from places
where [date] >= '2011-03-15' AND
[date] < '2011-03-16' AND
city = 'chicago';
精彩评论