On 9/28/11 9:41 AM, Ken Irwin wrote:
> SELECT distinct institution from renewals
> WHERE institution not in
> (SELECT distinct institution FROM `renewals` WHERE snap_date<  '2011-07-01')
>
> ...only it doesn't seem to work. Or rather, the query has been running for several minutes and never comes back with an answer. Each of these two queries takes just a few milliseconds to run on its own.
>
> Can someone tell me (a) am I just formatting the query wrong, (b) do subqueries like this just take forever, and/or (c) is there a better way to do this? (I don't really understand about JOIN queries, but from what I can tell they are only for mixing the results of two different tables so I think they might not apply here.)

What are the indices on the table? Have you already tried running the 
query through EXPLAIN?

Also, subqueries can often be sluggish in MySQL. This article does a 
succinct but decent job of explaining: 
http://www.mysqlperformanceblog.com/2010/10/25/mysql-limitations-part-3-subqueries/

--VMB

--
Vicky Brasseur
Product Manager, Digital Archive Service
Internet Archive
http://archive.org
[log in to unmask]