Delete query optimization

Question

I have a delete query who work well with small amount of row. But now with about 80K row it's failling due to "timeout"

My two table are like that :

TableA
------

| Column     | type       |
|------------|------------|
| link_id    | bigint(40) |
| product_id | int(10)    |
| parent_id  | int(10)    |

Table B
-------

| Column    | type    |
|-----------|---------|
| id        | int(11) |
| parent_id | int(11) |
| child_id  | int(11) |

I do a query like that to make a delete

DELETE FROM TABLEA
WHERE link_id IN (
    SELECT link_id FROM (
        SELECT link_id, parent_id, product_id FROM TABLEA
        UNION ALL
        SELECT id, parent_id, child_id FROM TABLEB
    ) tbl
    GROUP BY parent_id, product_id
    HAVING count(*) = 1
    ORDER BY parent_id
) ;

But whould not be the most optimized one.

The goal is to delete from table A all records not present in table B for the couple "parent_id / child_id".

in Table A the column "product_id" is the "child_id".

Thanks

what indexes are on these tables? What does `EXPLAIN {query}` show? — danblack, May 09 '19 at 07:13
For table A index Index PRIMARY link_id UNIQUE product_id, parent_id INDEX parent_id And table B there is only an index on PRIMARY id — Mitchum, May 09 '19 at 07:31
How small is the _small amount of row_? Could you do `LIMIT`? — FanoFN, May 09 '19 at 07:52

score 0 · Accepted Answer · answered May 09 '19 at 07:21

0

One way to solve this problem

DELETE A  FROM TABLEA A
INNER JOIN 
(    SELECT link_id FROM 
    (
        SELECT link_id, parent_id, product_id FROM TABLEA
        UNION ALL
        SELECT id, parent_id, child_id FROM TABLEB
    ) tbl
    GROUP BY parent_id, product_id
    HAVING count(*) = 1)B
ON A.link_id=B.link_id
where   A.link_id is not null;

answered May 09 '19 at 07:21

vishal

352
2
12

I got an error (I'm on mariadb) and cant change the "sql_mode" option... `Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tbl.link_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` – Mitchum May 09 '19 at 07:38
change you sql_mode through command or config file https://craftcms.stackexchange.com/questions/12084/getting-this-sql-error-group-by-incompatible-with-sql-mode-only-full-group-by/12106 and also check index on Link_id – vishal May 09 '19 at 07:41
There are a lot of posts in SO explaining why `sql_mode=only_full_group_by` is necessary. This is one > https://stackoverflow.com/a/34115425/10910692 . But if you only disable it temporarily just to perform the `DELETE`, why not. – FanoFN May 09 '19 at 08:06

score 0 · Answer 2 · answered May 09 '19 at 07:23

0

I would use a NOT EXISTS to get the link_id that don't have any relation in tableb

DELETE FROM TABLEA
WHERE link_id IN (SELECT link_id 
                  FROM TABLEA a
                  where not exists (select link_id 
                                    from tableb b
                                    where b.parent_id=a.parent_id and b.child_id=a.product_id))

answered May 09 '19 at 07:23

nacho

5,280
2
25
34

with that one I gon an error : You can't specify target table 'TABLEA' for update in FROM clause – Mitchum May 09 '19 at 07:35

Delete query optimization

2 Answers2