Had a good look at this now and doing: delete from tableB where not exists (select id from tableA where tableA.id = tableB.id)
Is indeed quite a bit faster than doing: delete from tableB where id not in (select tableA.id from tableA) In my case about 3 times as fast. Looking at the query plan with EXPLAIN QUERY PLAN was a good tip! I think though that I gained more by looking more carefully when to put the index on the id column. I need the index, but not before the delete, so I gained a lot by creating the index after the delete. This meant less rows to index plus less work to be done with the delete. Probably there will be more places in my app where looking at the timing of the index creation will speed things up, so thanks again for the advice. RBS -----Original Message----- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 26 March 2007 18:16 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Any way to do this faster? [EMAIL PROTECTED] wrote: > > I will need an index on that field later, but > I could drop it prior to the delete and create > a new one after. > > Don't do that. If you need the index, then leave it as is. > 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? > > Indexes are updated automatically as records are added and deleted from a table, that's why they add overhead if they are not serving some purpose. Your index will be correct after you delete the records from tableB. HTH Dennis Cote ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
