Why not use and INNER or LEFT join instead of a subquery?  Typically that
will be faster. Also, if the data set from either table is large I would
recommend indexes on the tables.

--
Charles Haines
Senior Code Monkey
P: (410) 535-5590 x1196

Recorded Books, LLC
http://www.recordedbooks.com

"Theory is when you know something, but it doesn't work. Practice is when
something works, but you don't know why. Programmers combine theory and
practice: Nothing works and they don't know why."



On Wed, Sep 28, 2011 at 12:41 PM, Ken Irwin <[log in to unmask]> wrote:

> Hi all,
>
> I've not done much with MySQL subqueries, and I'm trying right now with
> what I find to be surprising results. I wonder if someone can help me
> understand.
>
> I have a pile of data that with columns for "institution" and "date".
> Institution gets repeated a lot, with many different dates. I want to select
> all the institutions that *only* have dates after July 1 and don't appear in
> the table before that. My solution was to do a first query for all the
> institutions that DO have dates before July 1
> SELECT distinct institution FROM `renewals` WHERE snap_date < '2011-07-01'
>
> And then to do a SELECT query on all the institutions:
> SELECT distinct institution from renewals
>
> And then try to do a NOT IN subquery subtracting the smaller query from the
> larger one:
>
> 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.)
>
> Any advice would be most welcome.
>
> Thanks
> Ken
>