Let's say i have two tables:


    id |  article_id  |  text


    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?

  • Just thought of some questions... What are you wanting to do with this data once you have it? Are you going to have to repeat this operation when the data changes? – ErichBSchulz Jan 23 '14 at 8:56
  • @ErichBSchulz no - only once – brabertaser19 Jan 23 '14 at 22:44
  • ok - just thought of another way that old mysql engines will optimize more reliably (see bottom of my answer)... creating a new table will be quicker if you are deleting most recorcds, if you're only deleteing a few records then using the delete options will be quicker – ErichBSchulz Jan 24 '14 at 3:16

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

  • what do you mean by old? – brabertaser19 Jan 21 '14 at 11:00
  • I know in recent Maria db versions (last 2 years??) that IN performance was fixed, but historically mysql hasn't been good at optimising In and I'm not sure if it even has been fixed in mysql. – ErichBSchulz Jan 22 '14 at 7:40
  • so what you advice to try 1 or second? – brabertaser19 Jan 22 '14 at 8:02
  • 1
    I just noticed this question (and answer) and did not downvote but I guess it was (downvoted) because it seems you are not exactly sure which (from your suggestions) is the best thing to do. And when someone as the OP tries to run one of the suggestions - in tables having billions of rows - they may stuck with long-running operations. – ypercubeᵀᴹ Jan 23 '14 at 12:13
  • 1
    Mmm there is always stackoverflow.com/questions/3787651/…. But without benchmarking with engine in use and the actual dataset who can know which is best? The percentage of records being deleted isn't stated. Neither is the mysql version. Just down voting my answer without even leaving a comment seems not very usefull... – ErichBSchulz Jan 23 '14 at 13:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.