Delete in big table data all data, where foreign_id in other table didn't exist [closed]

Question

Let's say i have two tables:

table_a:

    id |  article_id  |  text


article:

    id  |  text

table_a for example contain data:

1 1 ""
2 1 ""
3 1 ""
4 2 ""
5 3 ""

and table articles such data:

1 ""
3 ""

as result i must in table_a left only such data:

1 1 ""
2 1 ""
3 1 ""
5 3 ""

but if i write such sql code (pseudo):

 delete from table_a where article_id not in (article.id)

but main trouble is that table_a contain 40 billion entries, and articles 2 billion, and if for each table_a entry i will look up which id's are in articles and collect and then check them - it will be very long... does mysql contain any variables, which could once be filled, and then used for each table_a entry?

how could i do this task more quick, than i imagine?

Answer 1

This should do it:

delete from table_a where article_id not in (select id from article)

A variant that maybe faster in older versions

delete a.* from table_a a
left join article on a.article_id = article.id
where article.id is null

Oh and regarding the second part of your question... You may want you put another column on table_a, like checked that you can set. And of course you need to put an index on article.id.

It occurs to me that if you are going to be deleting a good fraction of the records then it will be faster to simply make a new table which is a subset of the original.

create table table_b as 
select * from table_a where article_id in (select id from article)

Mmm... actually given the problem with old version IN optimization this is probably the best query to go with

create table table_b as 
select  a.id, a.article_id, a.text
from table_a a
inner join article on a.article_id = article.id

And just make sure you have an index on article.id (an index on table_a.article_id won't hurt either)

(just an addendum regarding the changing status of IN clause optimisation - I cannot find recent specific benchmarks on googleland but relevant discussion is here for Mysql and Maria