I create a DB and only one table in the DB. There are 3641043 records in the DB file. Min id is 27081364, Max id is 30902585.
I did follow operation: sqlite> delete from XXX where userId>30900000 and userId<30902000; took 1’32’’ sqlite> delete from XXX where userId>29000000 and userId<29902000; spent 3 hours and 33minutes and 26secs The table schema: CREATE TABLE XXX ( userId integer primary key, userName varchar not null, c1 varchar not null, c2 bigint not null, c3 varchar, c4 varchar not null, c5 varchar, c6 varchar, c7 bigint default 0, c8 bigint default 0, c9 integer default 0, c10 integer default 0, c11 integer default 0, c12 bigint default 0, c13 bigint default 0, c14 integer default 0, c15 integer default 1, c16 varchar, c17 varchar, c18 varchar , c19 integer default 0, c20 varchar default '', CONSTRAINT xxx_key UNIQUE (userName, c1, c4, c7) ); CREATE INDEX idx_1 on XXX(c7); CREATE INDEX idx_2 on XXX(username,c8); CREATE INDEX idx_3 on XXX (c5, c8); CREATE INDEX idx_4 on XXX (userName); CREATE INDEX idx_5 on XXX (c1); CREATE TRIGGER xxx_limit_size after insert on XXX when ((select max(userId) from XXX) - (select min(userId) from XXX) > 10000000) begin delete from XXX where userId < 1000 + (select min(userId) from XXX); end; The table is complex. Penny _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
