Thanks, will try that and report back. RBS
> I'm not sure if SQLite support this syntax, but try following statement, > > Delete from tableB b > Where not exist ( select 'x' > from tableA a > where a.id = b.id ) > > -----Original Message----- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: 26 March 2007 16:12 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Any way to do this faster? > > > RB Smissaert wrote: >> Simplified I have the following situation: >> >> 2 tables, tableA and tableB both with an integer field, called ID, > holding >> unique integer numbers in tableA and non-unique integer numbers in > tableB. >> Both tables have an index on this field and for tableA this is an > INTEGER >> PRIMARY KEY. >> Now I need to delete the rows in tableB where this number doesn't > appear in >> the corresponding field in tableA. >> >> Currently I do this with this SQL: >> >> Delete from tableB where ID not in (select tableA.ID from tableA) >> >> When table tableB gets big (say some 100000 rows) this will get a bit > slow >> and I wonder if there is a better way to do this. >> >> RBS >> >> >> >> >> >> >> > ------------------------------------------------------------------------ > ----- >> To unsubscribe, send email to [EMAIL PROTECTED] >> > ------------------------------------------------------------------------ > ----- >> >> >> > Your query is doing a complete table scan of tableA for each record in a > > table scan of tableB. > > SQLite version 3.3.13 > Enter ".help" for instructions > sqlite> create table tableA(id integer primary key, b); > sqlite> create table tableB(id, c); > sqlite> create index b_id on tableB(id); > sqlite> explain query plan delete from tableB where id not in (select > tableA.id > from tableA); > 0|0|TABLE tableB > 0|0|TABLE tableA > > You can improve this greatly using correlated subquery that will use the > > primary key index on tableA to find any matching records. > > sqlite> explain query plan delete from tableB where not exists (select > id from t > ableA where tableA.id = tableB.id); > 0|0|TABLE tableB > 0|0|TABLE tableA USING PRIMARY KEY > > Note that your index on tableB.id is not used and could be eliminated > unless it serves another purpose. > > HTH > Dennis Cote > > ------------------------------------------------------------------------ > ----- > To unsubscribe, send email to [EMAIL PROTECTED] > ------------------------------------------------------------------------ > ----- > > > THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE > PRIVILEGED, CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM > DISCLOSURE. If the reader of this message is not the intended recipient, > you are hereby notified that any dissemination, distribution, copying or > use of this message and any attachment is strictly prohibited. If you have > received this message in error, please notify us immediately by replying > to the message and permanently delete it from your computer and destroy > any printout thereof. > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
