From: Date: October 2 2007 3:56pm
Subject: delete takes too much time.
List-Archive: http://lists.mysql.com/maxdb/28829
Message-Id: <BAY142-W43482671FB5D9B8E80C95EAEAE0@phx.gbl>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="_f1dd54fb-8b94-49f1-a300-6d97cf997d67_"

--_f1dd54fb-8b94-49f1-a300-6d97cf997d67_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


 =3D=3D=3D=3DLong time ago=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D


Mark wrote:


> I have my app almost complete on MaxDB. I have just run=20


> into a major snag=20


> and would appreciate any help you can offer.=20


>=20


> I have a table "Files" that has about 400,000 rows=20


>=20


> If I run queries like=20


> 1) SELECT * FROM FILES WHERE FILE_NAME =3D "THISFILE.TXT"=20


> OR=20


> 2) SELECT * FROM FILES WHERE FILE_ID =3D 687=20


>=20


> I get the results in a snap=20


>=20


> However If I try=20


>=20


> DELETE FROM FILES WHERE FILE_NAME =3D "THISFILE.TXT"=20


>=20


> This takes about 5 minutes. I thought it might be an index=20


> thing so I tried=20


>=20


> DELETE FROM FILES WHERE FILE_ID =3D 687=20


>=20


> and it still took over 5 minutes. FILE_ID is the PRIMARY KEY=20


> so I know it=20


> is already indexed. I don't think a delete should take this=20


> long. Please=20


> help.=20






Then Becker, Holger responded:




Did you check the possibility of lock collisions on the table.=20


You should have a look into system table lockstatistics.=20




Then Mark said:




Under the Information - Locks in the database manager I see=20


row Locks 312776=20


Table Locks 6487=20


These seem kind of high for what I'm doing. How do I prevent these from=20


occurring?=20




=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D


However, the final result is unknown.


I have the similar problem. and the problem might not come from locks.=20


Since restart the database, and then i am sure no connection to it at all.


then i open SQL Studio to delete one row, it took 30 minutes to get done.




Is there anyone know what might be the problem?




How can i check the unique index on the primary key is ok?


I have check all indexes, even recreated them.


but i am not sure if the specific index on the primary key exists.


or is it relevant?






Thanks for your respond in advance!




Jack
_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/=

--_f1dd54fb-8b94-49f1-a300-6d97cf997d67_--