0

I have a simple tag_map

CREATE TABLE tag_map
(
article_id int(11) unsigned NOT NULL,
tag_id int(11) unsigned NOT NULL,
INDEX(tag_id),
PRIMARY KEY(article_id,tag_id)
) ENGINE=InnoDB

There is a table for interlinking the tags (some are parents of the other)

CREATE TABLE tag_parents
(
tag_id int(11) unsigned NOT NULL,
parent_id int(11) unsigned NOT NULL,
INDEX(parent_id),
PRIMARY KEY(tag_id,parent_id)
) ENGINE=InnoDB

I want to delete any row from tag_map if there is a link with the parent of that tag.

For example,

article_id    tag_id
1             1
1             2
1             3
2             3

tag_id      parent_id
3           2
1           1

I need to delete any article with tag_id 3 if it is already linked to its parent tag_id 2.

Output:

article_id    tag_id
1             1
2             3

I thought of

DELETE a FROM tag_map a 
    JOIN tag_map b ON a.article_id=b.article_id 
    JOIN tag_parents c ON a.tag_id=c.tag_id AND b.tag_id=c.parent_id

Since these are huge tables, I look for a better query in terms of performance.

4
  • Is your example output the rows you want deleted or the rows you want kept after? Have you tried your DELETE statement and how long does it take?...it's pretty close to what you'd want.
    – J.D.
    Commented Apr 14, 2022 at 11:37
  • @J.D. I could not complete the query after many hours. tag_parents is not big (20K rows), but tag_map is almost 1 billion rows. The first JOIN is very slow.
    – Googlebot
    Commented Apr 14, 2022 at 11:53
  • Is your example output the rows you want deleted or the rows you want kept after?
    – J.D.
    Commented Apr 14, 2022 at 13:55
  • @J.D. they are the rows that should remain (having the highest level in the tag_parents) after DELETE.
    – Googlebot
    Commented Apr 14, 2022 at 14:25

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.