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