Thank you MikeW. I try to use transaction, still slow. Use limit clause is good idea, but the help is limited. Dose number of index affect speed too? How many index on one table is better?
Regards, Penny PennyH wrote: > > 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 > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Sqlite3-delete-action-is-too-slow-tp20163980p20201132.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
