[sqlite] DELETEs using a range from an indexed column

[Tomas Lee]

I have this schema:

CREATE TABLE members
  (uid INTEGER PRIMARY KEY AUTOINCREMENT,
   name TEXT,
   score INTEGER);
CREATE INDEX members_score_index ON log (score);

I want to delete the 1000 members with the lowest scores.  Assume that
it is extremely unlikely for two members to have identical scores.
Also, the 1000 is arbitrary -- it could be more or less on different
days.  But each time I do this deletion, it will be a fixed number.

What's the fastest way to do this?  I want to lock the database for as
little time as possible.  Would it be:

Method A:

 * find the 1000th lowest score:

   SELECT score FROM members ORDER BY score LIMIT 1 OFFSET 999;

 * delete the records equal to or lower than that score

   DELETE FROM members WHERE score <= $thousandth_lowest_score;

Or would it be:

Method B:

 * find the uids for the 1000 lowest scores:

   SELECT uid FROM members ORDER BY score LIMIT 1000;

 * delete those records

   DELETE FROM members WHERE uid IN ([join $uids ,]);

     or:

   foreach doomed_uid in $uids do:
      DELETE FROM members WHERE uid = $doomed_uid

Or would it be:

Method C:

 * delete the records as you find them:

   sqlite3_prepare_v2(db, "DELETE FROM members WHERE uid = ?", -1, &stmt_d, 
NULL);
   sqlite3_prepare_v2(db, "SELECT uid FROM members ORDER BY score LIMIT 1000", 
-1, &stmt_q, NULL);
   while (sqlite3_step(stmt_q) == SQLITE_ROW) {
       int uid = sqlite3_column_int(stmt_q, 0);
       sqlite3_bind_int(stmt_d, 0, uid);
       sqlite3_step(stmt_d);
       sqlite3_reset(stmt_d);
   }
   sqlite3_finalize(stmt_d);
   sqlite3_finalize(stmt_q);

Or perhaps something else entirely?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users