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
n
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 now with what I
find to be surprising results. I wonder if someone can help me understand.
At Wed, 28 Sep 2011 12:41:26 -0400,
Ken Irwin 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".
opment Specialist
Falvey Memorial Library
Phone: 610-519-8954
Email: david.us...@villanova.edu
-Original Message-
From: Code for Libraries [mailto:CODE4LIB@LISTSERV.ND.EDU] On Behalf Of Ken
Irwin
Sent: Wednesday, September 28, 2011 12:41 PM
To: CODE4LIB@LISTSERV.ND.EDU
Subject: [CODE4
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
"Theor
aries.rutgers.edu/
- Original Message -
From: "Ken Irwin"
To: CODE4LIB@LISTSERV.ND.EDU
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
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 w
Ken,
If I understand the logic correctly, you need a list of institutions with dates
after July 1, but only institutions that have records existing before July 1.
Subqueries could work, but a view might be easier to work with, especially if
you plan to a lot of queries with similar logic.
Fir
Subqueries in MySQL are murder. Terribly slow. I never, ever use them.
Use a JOIN or do two queries.
Tim
On Wed, Sep 28, 2011 at 12:41 PM, Ken Irwin 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 s
> 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
a Mountain Community College
-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
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 wrote:
> Hi all,
>
> I've not done much with MySQL subqueries, and I'm trying right now with what
> I find to be surprisin
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
(SELE
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 Bü
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 wi
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 w
16 matches
Mail list logo
