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