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?

On 8/20/2021 1:34 PM, 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?
>
>

How big are your tables? Do you have indexes on id1 and id2 in their
appropriate tables?

--
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================

On 8/20/2021 2:00 PM, Jerry Stuckle wrote:
> On 8/20/2021 1:34 PM, 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?
>>
>>
>
> How big are your tables?

In this case:
parenttbl nearly 3M rows
childtbl nearly 3M rows
othertbl 130K rows

> Do you have indexes on id1 and id2 in their
> appropriate tables?

Yes.

MariaDB selects are fast, but a delete query as above required 1.25
minutes in MariaDB, but 0.3 seconds in SQLite (identical
SQL/tables/structures/indexes/starting rowcounts).

Depending on the table, select count(*) in MariaDB is also agonizingly
slow: eg 1.67 minutes vs 1 second in SQLite for a table of 3 int columns
(nearly 15M rows)

SQLite is no MariaDB, I understand that, but geez.

I researched the slow MariaDB DELETE and row counting, and there're no
easy answers.

https://www.google.com/search?q=mariadb+slow+delete

Apparently the same issues occur in PostgreSQL.

On 20.08.2021 19:34, DFS wrote:

> delete from childtbl
> where id1 in
> (
>   select id1
>   from parenttbl
>   where id2 in
>   (
>    select id2
>    from othertbl
>    where condition
>   )
> )

> How can I speed them up?

Show the EXPLAIN for the query. Then we can talk.

https://mariadb.com/kb/en/explain/

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

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!