0

So I have a large table(Table1), with about 5 million records. I have a second table(Table2) with about 108 million records. I am using a key value from Table1 to find any matching records in Table 2( there will be between 10-150 records per match). I am creating a new table for those records and deleteing them from Table2. Like a backup except not all records in Table2 will match something from table1. I have tried a whole match "select * from" which is way too large and have tried going one record at a time from Table1 but calculated it would take 35 days to run after letting it run for 2 days. So I guess i am looking for a good solution for handling large tables that isn't going to take forever and that doesn't run out of memory.

Thanks, feel free to point me to another question if it matches but i couldn't find any

  • I think, you should ask this question in DB community. Ask the people who knows how to optimize MySql databases. I am sure, this resolution will be depended on accurate table partitioning, indexing, etc. – T.S. Feb 10 at 3:47
  • I.e. your goal is to split table2, finally you want to have table2 where no one record matches to any record in table1, and table3 where any record matches to some record in table1, is it? Or, maybe, your task is backward - you want to move the non-matched records from table2 to table3? – Akina Feb 10 at 4:57
  • Each record in table1 has an ID. That ID use used to match multiple records in table2. For any record in table2 that matches an ID from table1 I want to place it in table3 and remove it from table2. I have had it working a couple ways but run into a memory issue if I run a willdcard search and would take a month to run if i do a row at a time. – robby Feb 10 at 5:44
2

You can try this:

create table Table3 as (select * from Table1 A where 
exists(select * from Table2 B where A.key=B.key));

And then delete from Table2 using newly created table Table3

delete from Table2 A where exists(select * from Table3 B where A.key=B.key);
  • this worked perfectly. thank you! – robby Feb 12 at 2:45
0

If I understand the task correctly (move matched records into table3), then

CREATE TABLE table3 LIKE table2;
INSERT INTO table3
SELECT * 
FROM table2
JOIN table1 USING (some_key);
DELETE table2.*
FROM table2
JOIN table1 USING (some_key);
  • I will try organizing it like this, but my issue has been that the select statement runs out of memory(SELECT * FROM ..)... there are 4.6 million records in it and 108 million in the second table. would this still be an issue with the way your query is set up? I was not using a join and unsure of how the memory allocation for that differs in a join compared to getting a result and then running a second query against that. – robby Feb 10 at 5:41
  • @robby the select statement runs out of memory(SELECT * FROM ..)... Who, what software, produces this "out of memory" error? It seems it's not MySQL error... – Akina Feb 10 at 5:44
  • Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 632 bytes) This is the error I get on the page running from a php script – robby Feb 10 at 5:51
  • 1
    @robby Oh, you try to get millions of records into PHP? of course the problem occures. Perform the action on the MySQL side, without retieving the records into PHP as shown above. – Akina Feb 10 at 5:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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