I'm trying to figure out why my deletes are taking so long. It _might_
have started with 3.2.5, but not sure.
I'm using 3.2.5 on Windows XP SP2 on a very fast machine. The XInfo
table (shown below) has quite a few indices because it is selected on in
every imaginable way:
CREATE TABLE XInfo
(
XInfoID INTEGER PRIMARY KEY,
ScanID INTEGER NOT NULL,
XNameID INTEGER NOT NULL,
OwnerID INTEGER NOT NULL,
XID INTEGER NOT NULL,
Size INTEGER NOT NULL,
CreatedDate INTEGER NOT NULL,
LastAccessedDate INTEGER NOT NULL,
LastModifiedDate INTEGER NOT NULL
);
CREATE INDEX Ind_XInfo_ScanID ON XInfo (ScanID);
CREATE INDEX Ind_XInfo_OwnerID ON XInfo (OwnerID);
CREATE INDEX Ind_XInfo_XNameID ON XInfo (XNameID);
CREATE INDEX Ind_XInfo_Size ON XInfo (Size);
CREATE INDEX Ind_XInfo_CreatedDate ON XInfo (CreatedDate);
CREATE INDEX Ind_XInfo_LastAccessedDate ON XInfo (LastAccessedDate);
CREATE INDEX Ind_XInfo_LastModifiedDate ON XInfo (LastModifiedDate);
There are roughly 250,000 rows in the table.
The database file is about 230MB (lots of other data besides this
table).
There are about 11,000 rows with a ScanID=16;
The command:
DELETE FROM Xinfo WHERE ScanID=16
takes over 2 minutes to complete. Within the first second or two a 9 MB
journal file is created, and then it doesn't grow any further. CPU
usage is around 1-2% for the duration.
It operates correctly, but is surprising slow compared to my previous
experience. And the fact that the CPU is nearly idle and no real disk
activity that I can hear makes me think there are perhaps some locking
issues going on, but I really don't know. I tried running ANALYZE, but
it didn't have any noticable effect.
Are there just too many indices??
Thanks in advance for any help.
Doug
-----------------------------------------------------------------
Visit our Internet site at http://www.reuters.com
To find out more about Reuters Products and Services visit
http://www.reuters.com/productinfo
Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.
