From Newsgroup: comp.databases.mysql

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?


--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

On 8/20/2021 1:34 PM, DFS wrote:

Deletes of this type are very slow:

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

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
==================
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

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
(
-a-a select id1
-a-a from parenttbl
-a-a where id2 in
-a-a (
-a-a-a select id2
-a-a-a from othertbl
-a-a-a where condition
-a-a )
)


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.

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

On 20.08.2021 19:34, DFS wrote:

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

How can I speed them up?

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

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

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

On 20/08/2021 19.34, DFS wrote:

Deletes of this type are very slow:

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

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

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

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
(
-a-a select id1
-a-a from parenttbl
-a-a where id2 in
-a-a (
-a-a-a select id2
-a-a-a from othertbl
-a-a-a where condition
-a-a )
)


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(
-a-a-a-aSELECT id1 FROM parenttbl
-a-a-a-aINNER JOIN othertbl ON othertbl.id2 = parenttbl.id2
-a-a-a-aWHERE 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!
--- Synchronet 3.21d-Linux NewsLink 1.2