 |
 |
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 >