Deleting from huge database

Question

I'm deleting data from database which is about 1.8GB big. (through C# app)

The same operation on smaller databases (~600MB) run without problem, but on the big one I'm getting:

Lock wait timeout exceeded; try restarting transaction.

Will innodb_lock_wait_timeout fix the problem or there is another way?

I don't think that optimizing queries is a solution, because there is no way to make them simpler.

---

I'm deleting parts of the data on conditions and relations, not all the data.

Answer 1

Right Click the table--> Script Table As --> Create To --> New query.. save the query...

Right click the table --> delete

Refresh your Database and Intellisense to forget the table and then run the script which will recreate the table and that's how you have an empty table...

Or you can simply increase the setting for innodb_lock_wait_timeout (or table_lock_wait_timeout, not sure which) if you dont want to delete all the info in the table

Answer 2

You can split the delete statement into smaller parts that wont timeout.
Like delete these stuff from id 1 to 1000 ,execute and commit, do the same for ids 10.000 -20.000 and etc..

Answer 3

If you're deleting all the rows in the table, use

 Truncate table *tablename* 

The delete command uses the transaction log when completing the task, but truncate cleans it out without logging.

Answer 4

You mentioned that you were '...deleting parts of the data based on some conditions and relations, not all the data'. I would check that there are appropriate indexes on all the keys you are using to filter the data to delete.

If you were to show us your schema and where clause we could suggest ones that may help.

You should also consider splitting your delete into multiple batches of smaller numbers of rows.

Another alternative would be to do a SELECT INTO, with only the data you want to keep into another table, drop the original, then rename this new table.