Deleting rows with constraint on indexed column (non unique) mySQL InnoDB

user_10501730 0
Viewed 41

I'm not very experienced with databases and don't want to mess up. Will be great if get some help.

Running mySQL 5.7 (InnoDB)

Indices of interest:

  • column_1 -> unique index (primary index)
  • column_2 -> non unique index
  • column_3 -> non unique index
Want to delete all rows with column_2 value 'randomString'

Command:
delete from mydb.myTable
where column_2 = 'randomString'
  • number of rows where column_2 = randomString = 2k
  • total number of rows in the table = 400K

There's a system System2 continuously reading from this DB looking for rows with column_3 value readValue

  • number of rows where column_2 = randomString and column_3 = readValue = 1.8K

Questions

Which rows will the delete statement mentioned above lock ? Is it only the rows with randomString ? In which case, will it also block the connections trying select * with constraint on column_3 till the lockwait timeout ?

or

What is the best way to safely delete the rows which have column_2 as randomString without blocking System2 ??

innodb mysql
1 Answers

That delete will lock all 2K rows until it is finished, which might be a noticeable length of time.

Here are several tips on how to do big deletes with less impact. (You have not provided enough details for me to point to a specific suggestion.) http://mysql.rjweb.org/doc.php/deletebig

user_17668310
Related