Slow deletes (MariaDB)

4 views
Skip to first unread message

DFS

unread,
Aug 20, 2021, 1:34:15 PMAug 20
to
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?


Jerry Stuckle

unread,
Aug 20, 2021, 2:00:51 PMAug 20
to
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
jstu...@attglobal.net
==================

DFS

unread,
Aug 20, 2021, 4:09:11 PMAug 20
to
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.

Axel Schwenke

unread,
Aug 20, 2021, 5:20:03 PMAug 20
to
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/

J.O. Aho

unread,
Aug 20, 2021, 6:01:57 PMAug 20
to
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

DFS

unread,
Aug 21, 2021, 12:04:39 AMAug 21
to
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!
Reply all
Reply to author
Forward
0 new messages