And don't forget to try using EXPLAIN to figure out what is going on with the
query. This will tell you which indexes are being used, whether indexes are
being ignored, the approximate number of rows being parsed, etc. Just add
EXPLAIN to the front of the SELECT query. I find the EXPLAIN output
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
Hi..
You could consider at least two things
1. drop the distinct keyword in the subquery. You dont need it
2. Use a EXISTS keyword instead of not in - check
http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html.
It is normally considered more effective
§;heb
Hans Erik
Sub queries are not well optimised till very recently therefore
rewrite subquery as a join for speed.
eg for A not in B the following
SELECT a.* FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL;
also if you have two sets from the same table use derived tables and
then join them
I must be missing something. Why wouldn't you just do:
SELECT distinct institution
FROM renewals
WHERE snap_date = '2011-07-01'
On Wed, Sep 28, 2011 at 9:41 AM, Ken Irwin kir...@wittenberg.edu wrote:
Hi all,
I've not done much with MySQL subqueries, and I'm trying right now with what
I
-Original Message-
From: Code for Libraries [mailto:CODE4LIB@LISTSERV.ND.EDU] On Behalf Of Ken
Irwin
Sent: Wednesday, September 28, 2011 9:41 AM
To: CODE4LIB@LISTSERV.ND.EDU
Subject: [CODE4LIB] mysql subquery response time
Hi all,
I've not done much with MySQL subqueries, and I'm trying right
I want to select all the institutions that *only* have dates after July 1
How about:
select distinct institution
from renewals
where snap_date = '2011-07-01';
in Oracle:
where snap_date = to_date(20110701, 'MMDD');
(remove the = to not include 2011-07-01)
Hopefully I understood your
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
