[CLUG-tech] SQL Query Question

Hendrik Visage hvjunk at gmail.com
Wed Dec 19 09:53:17 SAST 2007


On Dec 19, 2007 9:18 AM, Mark Widdicombe
<MarkWiddicombe at woolworths.co.za> wrote:
> Hi Chris,
>
> What indexes do you have on each of the tables?  It may pay you to add a
> new boolean column to table1 to indicate whether or not the row has been
> processed.  Then you can just:-
>
> Select * from table1 where processed_ind = "N"

even on a second/thrid table keeping track of those "changes"

This whole thing smells like a design that needs to be changed, and in
the end it'll better to start looking at changing it earlier, rather
than later...

> > -----Original Message-----
> > From: clug-tech-bounces at clug.org.za
> [mailto:clug-tech-bounces at clug.org.za]
> > On Behalf Of Chris Meistre
> > Sent: Tuesday,18 December 2007 22:49
> > To: Technical Questions and Answers
> > Subject: [CLUG-tech] SQL Query Question
> >
> > Hi there,
> >
> > I have a table with more than 900,000 entries in.
> >
> > table1
> > url
> > text
> >
> > I also have another table that I put values about table1's info.
> >
> > table2
> > url
> > moretext
> >
> > For various reasons I cannot put the data together in one table, and
> > need to use 2 tables.   Once I do something to the values I put them
> in
> > table2.  And then I don't touch it again.
> >
> > To select the elements I haven't scanned yet I use the following
> query:
> >
> > select * from table1 where url not in (select url from table2);
> >
> > This gives me all the rows in table1 that haven't been put into
> table2.
> > Now, this works but takes ages to run.   Can anyone help me with a
> > better, quicker query?
> >
> > Thanks,
> >   Chris
> > --
> > clug-tech mailing list: clug-tech at clug.org.za
> > To (un)subscribe: http://lists.clug.org.za/mailman/listinfo/clug-tech
> > Wiki: http://wiki.clug.org.za
> > IRC: za.ethereal.web.za #clug
> > List Rules: http://wiki.clug.org.za/wiki/Mailing_list_rules
> --------------------------------------------------------------------------------
> Please note: This e-mail and its contents are subject to a disclaimer
> which can be viewed at http://www.woolworths.co.za/disclaimer. Should
> you be unable to access the link please e-mail disclaimer at woolworths.co.za
> and a copy of the disclaimer will be e-mailed to you.
>
> --
> clug-tech mailing list: clug-tech at clug.org.za
> To (un)subscribe: http://lists.clug.org.za/mailman/listinfo/clug-tech
> Wiki: http://wiki.clug.org.za
> IRC: za.ethereal.web.za #clug
> List Rules: http://wiki.clug.org.za/wiki/Mailing_list_rules
>



-- 
Hendrik Visage


More information about the clug-tech mailing list