any other way to do this "WHERE IN" + "WHERE NOT IN"

[dtra]

hi all

i have this code
where i'm trying to check if my msn contacts
are also contacts on my web site

i keep email addresses and msn addresses in the database
but obviously not everyone uses the same address as what they register with

so after they log into msn, i get all their msn addresses and then compare them to what is in our database
but this query seems to take a while
and since i have not had great experience with "WHERE (NOT) IN"
i wanted to know if there is anything else i can use that may be faster

the addresses in the "WHERE IN" part of taken from the msn login details
and so i don't know how else i can check them
and user_ID's are taken from another separate query from another table that stores contacts details
i guess i could probably try to integrate that into a subquery, but not sure if there are any performance gains to be had there

  SELECT user_Nick, user_ID, user_Email, user_MSN
 FROM urban_userlog
 WHERE (user_Email
 IN ( 'sample@domain.com', 'test@hotmail.com')
 OR user_MSN
 IN ( 'sample@domain.com', 'test@hotmail.com')
 )
 AND user_ID NOT 
 IN ( '1', '2')
 AND user_AccountStatus =  'a'
 GROUP  BY user_Nick

thanks
dave

[r937]

subqueries will always be faster than running one query and editing the results into a query string to run a second query

[dtra]

ok, thanks
but nothing i can do with the where in clauses?

[r937]

oh, there probably is

any time you use one query to create what's necessary to run a second query, you can probably do it all in a single query with a join

i'd need to see all your queries (and as little php as possible, because i don't do php)

[dtra]

 SELECT user_Nick, user_ID, user_Email, user_MSN FROM urban_userlog 
 	WHERE (user_Email IN (email@sample.com, sample@email.com) OR user_MSN IN (email@sample.com, sample@email.com)) 
 	AND user_ID NOT IN (SELECT user_ID FROM urban_userlog 
 		INNER JOIN urban_msndirectorycheck2 ON user_ID = msndirectorycheck_canLookAtUserID 
 		WHERE msndirectorycheck_userID = '$userID') 
 	AND user_ID <> '$userID' 
 	AND user_AccountStatus = 'a' 
 	GROUP BY user_Nick

basically the emails i don't think can be changed much, since they are not taken from the database
rather from a list generated from the php
but maybe the second userID part?

thanks
dave

[r937]

you might be able to do away with the join in the subquery, and simply check WHERE user_id NOT IN (SELECT msndirectorycheck_canLookAtUserID ...)

otherwise the query looks okay

[dtra]

oh ok
i'll have a look at that
thanks