1. Forum
  2. Usenet
  3. COMP.DATABASES.MYSQL

  • Slow deletes (MariaDB)

    From Jerry Stuckle@21:1/5 to DFS on Fri Aug 20 14:00:46 2021
    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
    [email protected]
    ==================

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From DFS@21:1/5 to All on Fri Aug 20 13:34:10 2021
    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?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From DFS@21:1/5 to Jerry Stuckle on Fri Aug 20 16:09:06 2021
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From J.O. Aho@21:1/5 to DFS on Sat Aug 21 00:01:54 2021
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Axel Schwenke@21:1/5 to DFS on Fri Aug 20 23:20:00 2021
    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/

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From DFS@21:1/5 to J.O. Aho on Sat Aug 21 00:04:35 2021
    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!

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)