Thanks for your input. Currently I am managing the foreign key links in my application and not within the database so this is a possibility to look into.
A stored procedure is also a good idea, however I must upgrade my MySQL version to >= 5.0. I am currently running an optimize on all tables each weekend so this is already taken care of. In your opinion would moving to a 64-bit OS and being able to use the memory better give a better performance increase ? Again, thanks for your help. Marvin. -----Original Message----- From: nigel wood [mailto:[EMAIL PROTECTED] Sent: 20 October 2006 00:02 To: Marvin Wright Cc: mysql@lists.mysql.com Subject: Re: Deletes on big tables Marvin Wright wrote: >I have 3 tables where I keep cache records, the structures are >something like > > >TableA is a 1 to many on TableB which is a 1 to many on TableC > >To give you an idea of size, TableA has 8,686,769 rows, TableB has >5,6322,236 rows and TableC has 1,089,635,551 rows. > > >My expiry runs on a daily basis and deletes approximately this number >of rows from each table. > > > > TableA 500,000 > > TableB 4,836,560 > > TableC 71,534,549 > > > My suggestions: 1) Add an (expirydate,primary key) composite index on table A and make sure your foreign keys are in place 2) Turn on cascading deletes for these three tables or (less optimally) use a single multi-table delete in a stored procedure to delete lots of rows (in A) per query not singles 3) run the delete query with a limit of 10000 rows or so in a transaction, use show innodb status to monitor how much rollback space the innodb engine has left and up the number rows if possible. 4) If your data integrity can take the risk turn off innodb's flush on commit for this connection during the deletes. 5) Run optimise table once a week, if your deleting lots of records for a while the database is probably fragmented. If nessasary for availablity use a DR slave/master and run optimise table on the DR pair before promoting them to live. Then look at the memory. If you cant upgrade the OS to use all that memory (we do on 32 bit servers) at least create a huge ram disk and tell MySQL to use it as temporary storage. Hope that helps Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
