 |
 |
I may be wrong about this, but when you use a "not in" operator, you're necessarily doing a table scan. Any indexes you have are not used in such a query, so you'll definitely see a performance hit. But if each independent query runs quickly, why not use a temporary table to store the results of what you've got written as a subquery first, and then use that presumably small, distinct list of institutions in the subquery afterward? Something like this: SELECT INTO #my_institutions SELECT distinct institution FROM `renewals` WHERE snap_date < '2011-07-01' EXEC SELECT distinct institution from renewals WHERE institution not in (SELECT institution from #my_institutions) That assumes you're able to SELECT INTO, but if not, just create yourself a my_institutions table first. \-/-\-/-\-/-\-/-\-/-\-/-\-/-\-/-\-/-\-/-\-/-\-/ Scot Colford Web Services Manager Boston Public Library [log in to unmask] Phone 617.859.2399 Mobile 617.592.8669 Fax 617.536.7558 On 9/28/11 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