# Large MySQL data table

**URL:** <https://forum.inductiveautomation.com/t/large-mysql-data-table/5547>\
**Category:** Ignition\
**Created:** [December 8, 2012, 11:42pm UTC](https://forum.inductiveautomation.com/t/large-mysql-data-table/5547 "2012-12-08T23:42:41Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Curlyandshemp](https://avatars.discourse-cdn.com/v4/letter/c/bcef8e/32.png) [@Curlyandshemp](https://forum.inductiveautomation.com/u/Curlyandshemp)\
**Post date:** [December 8, 2012, 11:42pm UTC](https://forum.inductiveautomation.com/t/large-mysql-data-table/5547/1 "2012-12-08T23:42:41Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![nathan](https://avatars.discourse-cdn.com/v4/letter/n/c5a1d2/32.png) [@nathan](https://forum.inductiveautomation.com/u/nathan)\
**Post date:** [December 9, 2012, 7:43am UTC](https://forum.inductiveautomation.com/t/large-mysql-data-table/5547/2 "2012-12-09T07:43:54Z")

</div>

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.

[mysql.rjweb.org/doc.php/deletebig](http://mysql.rjweb.org/doc.php/deletebig)  
[corpocrat.com/2011/02/07/mysql-d … long-time/](http://corpocrat.com/2011/02/07/mysql-delete-is-very-slow-and-takes-long-time/)

---

<div class="post-metadata">

**Author:** ![Curlyandshemp](https://avatars.discourse-cdn.com/v4/letter/c/bcef8e/32.png) [@Curlyandshemp](https://forum.inductiveautomation.com/u/Curlyandshemp)\
**Post date:** [December 9, 2012, 3:58pm UTC](https://forum.inductiveautomation.com/t/large-mysql-data-table/5547/3 "2012-12-09T15:58:19Z")

</div>

[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.

[mysql.rjweb.org/doc.php/deletebig](http://mysql.rjweb.org/doc.php/deletebig)  
[corpocrat.com/2011/02/07/mysql-d … long-time/](http://corpocrat.com/2011/02/07/mysql-delete-is-very-slow-and-takes-long-time/)[/quote]

Thanks Nathan,  
I will look into these.

Yes I did try using the ID column , primary key, to DELETE rows. Even thrying to delete 1 row , i get the timeout message.