Another way of saying what Dennis said (I had to read his reply twice before I understood it):
your query: for every record in TableB it returns ALL the records in tableA and then looks through them Dennis's query: for every record in TableB it checks tableA directly for the existence of that ID the speed increase should be dramatic also, as Dennis said .. unless you have some other reason for that index on Table B, get rid of it. Indexes slow down everything but database reads and you're not using it in this example. [EMAIL PROTECTED] wrote: > > Thanks Dennis, will try that when I get chance (at work now) and will > report back about the difference > it made. > > RBS > > > 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] > > ----------------------------------------------------------------------------- > > > > > > > > > > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
