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
