Re: Slow deletes (MariaDB)

On 20/08/2021 19.34, DFS wrote:
> Deletes of this type are very slow:
> 
> delete from childtbl
> where id1 in
> (
>    select id1
>    from parenttbl
>    where id2 in
>    (
>     select id2
>     from othertbl
>     where condition
>    )
> )
> 
> 
> Like 1.5 minutes to delete a couple thousand rows.
> 
> How can I speed them up?

Why not join the tables

DELETE childtbl
FROM childtbl
INNER JOIN parenttbl ON parenttbl.id1 =

childtbl.id1
INNER JOIN othertbl.id2 ON parenttbl.id2
WHERE othertbl[condition]


even

DELETE FROM childtbl
WHERE id1 IN(
	SELECT id1 FROM parenttbl
	INNER JOIN othertbl ON othertbl.id2 = parenttbl.id2
	WHERE othertbl[condition]
)

could be faster than the original.


Sure you should compare the explain result that others have already 
pointed out.


-- 

  //Aho