> also, as Dennis said .. unless you have > some other reason for that index on > Table B, get rid of it.
I will need an index on that field later, but I could drop it prior to the delete and create a new one after. Thinking about it I am not sure in any case of the value of an index after deletes on a table. Is it usually better to re-index after deletes? RBS > 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] > ----------------------------------------------------------------------------- > > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
