Timeout issue in MySQL Delete Query with IN Statement

I came across high delay timing issue while executing delete query in MySQL with IN statement as was taking too much time to execute, here is the case of timeout issue and solution to fix this issue.

I want to delete all records tagged as ‘Student’. So, I use below query to delete child table entries.

delete from child where parentid in (select id from parent where tag = 'Student');

Initially, it was running as normal but as the size of the data records increasing, this query abnormally started taking too much time to execute and started throwing timeout error on page. Then we checked indexing applied on table columns and changed also but didn’t get the success.

If I run the below query with static value passed into IN statement it works fast.

delete from child where parentid in (12);

but when I passed subquery (like below) into IN statement it takes time to execute.

delete from child where parentid in (select id from parent where tag = 'Student');

Later I found solution using table alias and JOIN statement.

delete c from child c inner join parent p on p.id = c.parentid where p.tag = 'Student';

Here we give alias name to tables and inner join both tables and delete record from table using its alias name. Also, ensure that parent.id and child.parentid both have an index on them to maximize the performance of the join.

Finally, it delay timing issue in executing the query got resolved using table alias and JOIN statement!

Hurray! This may help you fix similar issue!