Re: [CODE4LIB] Job Posting: Digital Library Repository Developer, Boston Public Library (Boston, MA)

Our Boston Public Library needs to develop a more open encouraging environment that we have folks with the best talents and skills! Begin with fomenting exchanges where current BPLers can be free to express their points of views. At this time BPLers are squelched by an oppressive environment of rel

Re: [CODE4LIB] ny times best seller api

On Wed, Sep 28, 2011 at 5:38 PM, Doug Yancey wrote: >I just got this set up on our site, using php to parse the json response. > You should be able to see the result on our staging port at: > >http://catalog.ccrls.org:2082 > >...under the 'Bestsellers' menu in the top nav bar. Nice. It reminds me

[CODE4LIB] VuStuff Conference, Villanova University, October 12th, 2011

Apologies for cross-posting: Greetings from the VuStuff Team, VuStuff is an annual conference on the intersection between scholarship and technology, held at Villanova University's Falvey Memorial Library. This year's conference, VuStuff II, will be held on Wednesday, Oct. 12, 2011 at Falvey

Re: [CODE4LIB] mysql subquery response time

Well, not being a MySQL whiz myself, I thought maybe the query was being needlessly slowed down by having DISTINCT specified in both parts of the query. In testing a similar query on one of my databases, the double DISTINCT query did run quite slowly. I tried modifying it to a form like this:

Re: [CODE4LIB] mysql subquery response time

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

Re: [CODE4LIB] mysql subquery response time

Ken, The list is locked at the moment, so this reply may take awhile to reach you, so sorry if this question has been answered already (or I'm too late). Anyway, here's how I'd format it (assuming I read the question right, that you want distinct institutions that have listing from July 1

Re: [CODE4LIB] mysql subquery response time

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

Re: [CODE4LIB] mysql subquery response time

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

Re: [CODE4LIB] ny times best seller api

Because a few people have asked me about this off list, and because I'm now struggling with a different related question, I'm bringing it back to the list for all. If it's TMI, just delete the email and move on... sorry to jam your inbox. But I'm determined to make this thing work. I have the proxy

Re: [CODE4LIB] mysql subquery response time

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

Re: [CODE4LIB] mysql subquery response time

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

Re: [CODE4LIB] mysql subquery response time

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

[CODE4LIB] Senior Application Developer opening

Columbia University's Center for Digital Research and Scholarship (CDRS) (http://cdrs.columbia.edu ) is seeking a *Senior Application Developer* to implement CDRS' online platforms by architecting, coding, integrating, and maintaining services and back-end application

Re: [CODE4LIB] Job Posting: Digital Library Repository Developer, Boston Public Library (Boston, MA)

Anyone want to apply to be Roy's nephew? On Wed, Sep 28, 2011 at 12:28 PM, Ya'aqov Ziso wrote: > *S... now that we've cleared this up - anyone want to apply?* > *===* > anybody can apply, but Roy's nephew will get the job . > * > * > * > * >

Re: [CODE4LIB] mysql subquery response time

> 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

Re: [CODE4LIB] ny times best seller api

On Wed, Sep 28, 2011 at 5:02 PM, Michael B. Klein wrote: > > It's not NYTimes.com's fault; it's the cross-site scripting jerks who made > the security necessary in the first place. > > NYTimes could allow JSONP, but then developers would need to embed their API key in their web pages, which means

Re: [CODE4LIB] mysql subquery response time

Ken, For your performance issue, it would be interesting to take out the distinct on your subquery and see if that has any effect. You definitely want distinct on the outer query to reduce the results, but on the subquery version, it may be preventing the query optimizer from taking some other

Re: [CODE4LIB] mysql subquery response time

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

Re: [CODE4LIB] ny times best seller api

I just got this set up on our site, using php to parse the json response. You should be able to see the result on our staging port at: http://catalog.ccrls.org:2082 ...under the 'Bestsellers' menu in the top nav bar. We're having to host the actual php pages on a separate web server, since our

Re: [CODE4LIB] mysql subquery response time

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

Re: [CODE4LIB] mysql subquery response time

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ü

Re: [CODE4LIB] mysql subquery response time

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