On 10/24/08, yhuang <[EMAIL PROTECTED]> 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
Penny, I guess the problem is that you are simply trying to do too much here. You can test this by timing the following SELECT SELECT * FROM xxx WHERE userId > 29000000 and userId < 29902000; Does that also take 3 hours 33 mins and 26 secs. My guess is probably yes. DELETE has to spend all that time looking for those rows (although, yes, 3 hours + does seem excessive). You can also try timing 'SELECT * FROM xxx WHERE userId < 29000000;'. If that is very fast, create a temp table with it. Then add to that temp table 'SELECT * FROM xxx WHERE userId > 29902000;'. Now you have a temp table which has the record you do want to retain. Now you can 'DROP TABLE xxx;' and that should be instantaneous. Then you can rebuild your xxx table with 'CREATE TABLE xxx AS SELECT * FROM temp_table;' Then you can rebuild your indexes. You have a bunch of indexes, all of which need to be recomputed every time the table changes. You could try dropping those indexes and see if your DELETE query speed changes. Since your userId is already an INTEGER PRIMARY KEY, it would automatically be indexed, so you don't have to be worry about that. > > > > 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 > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
