Slow deletes (MariaDB)

[DFS]

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]

Post by DFS
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?

[DFS]

Post by Jerry Stuckle

Post by DFS
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

Post by Jerry Stuckle
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]

Post by DFS
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]

Post by DFS
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.

[DFS]

Post by J.O. Aho

Post by DFS
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!