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.
DELETE
statement and how long does it take?...it's pretty close to what you'd want.tag_parents
is not big (20K rows), buttag_map
is almost 1 billion rows. The first JOIN is very slow.