 |
 |
Ken, It shouldn't take long to run. If you want to so the sub-select route maybe this will help? SELECT DISTINCT(institution) FROM `renewals` WHERE snap_date > '2011-07-01' AND institution NOT IN (SELECT DISTINCT(institution) from `renewals` where snap_date < '2011-07-01'); red vines > twizzlers -- Chad Mills Digital Library Architect Ph: 732.932.8573 x123 Fax: 732.932.1386 Cell: 732.309.8538 Rutgers University Libraries Scholarly Communication Center Room 409D, Alexander Library 169 College Avenue, New Brunswick, NJ 08901 http://rucore.libraries.rutgers.edu/ ----- Original Message ----- From: "Ken Irwin" <[log in to unmask]> To: [log in to unmask] Sent: Wednesday, September 28, 2011 12:41:26 PM Subject: [CODE4LIB] mysql subquery response time 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