Delete in big table data all data, where foreign_id in other table didn't exist 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?
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