I have a table of data that has almost 6,000,000 rows of data. The table has become so large that I cannot trim any old rows of data using MySQL Workbench and DELETE queries. End up getting error code 1205, timeout.
Even tried exporting to sql dump file ( 2 gigByte), but wordpad runs out of memory opening the file. Planned to export to dump file and manually delete old data from 1 year ago.
Is there any tools or methods to trim large database tables? I tried using ALTER TABLE Max_Rows within Workbench, but that ends up blowing out all the table data. Good thing I have a backup!
These articles discuss that scenario. According to the first, DELETE is a costly operation (for slightly different reasons) on both InnoDB (won’t lock table during operation, but has to create UNDO info) and MyISAM (locks table during delete).
I haven’t personally dealt with this issue on MySQL. However, there is a lot of info on the Internet on the topic. Have you tried adding a (row) LIMIT to your delete query? Also make sure your WHERE clause references an indexed column or primary key.
[quote=“nathan”]These articles discuss that scenario. According to the first, DELETE is a costly operation (for slightly different reasons) on both InnoDB (won’t lock table during operation, but has to create UNDO info) and MyISAM (locks table during delete).
I haven’t personally dealt with this issue on MySQL. However, there is a lot of info on the Internet on the topic. Have you tried adding a (row) LIMIT to your delete query? Also make sure your WHERE clause references an indexed column or primary key.