Re: Slow deletes (MariaDB)

On 8/20/2021 6:01 PM, J.O. Aho wrote:
> 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.


I've gotten gotten used to using the original syntax in SQLite for the 
past N years, and it's always fast.



original syntax in MariaDB:

delete from child
where id1 in
(
    select id1
    from parent
    where id2 in
    (
     select id2
     from other
     where condition
    )
)
Query OK, 2557 rows affected (49.864 sec)


orig syntax is about 4 seconds in SQLite.


new syntax in MariaDB:

delete c.*
from child c, parent p, other o
where c.id = p.id
and p.id2 = o.id2
and o.condition
Query OK, 2557 rows affected (0.489 sec)


new syntax won't execute in SQLite.


Thanks!