PHP / MYSQL: iterate slowly over 6k lines and create new records for each line - algorithm

Question

I apologize for the stupid question, but I have one of these days where I feel like the dumbest programmer. I need your help. I am currently developing PHP and MYSQL, where I, as a super low-skilled, work on an inherited project.

I have a database table with almost 6k records in it, say TABLE_A, and I need to iterate over the records in table A and for each record create two new records in table B, where PK from TABLE_A (Id) is FK in TABLE_B. Nothing special? Therefore, I have one more thing, this happens, please do not blame the production database. Therefore, I received a request to start inserting into table B for only 10 records every 1 second. In addition, I have a list of identifiers that looks like this: 1,2,4,6,7,8,9,11,12,15,16, .. up to 6k. Therefore, I can not basically do:

for ($i = 1; $i <= MAX(id); $i++) { //create two new records in TABLE B } 

I spent some time researching and I need to talk to you guys about this to come up with some ideas. I do not want an exact solution from you, but I want to learn how to think about it and how to come up with a solution . I thought about it on the way home. So I just created an algorithm in my head. Here is a step-by-step process in my head about what I know and what I will probably use:

• I know that I can run just 10 inserts in 1 second - so I need to limit the selection from TABLE A to just 5 rows in one batch.
• Therefore, I can use the MySQL syntax: LIMIT and OFFSET , for example: select * from t LIMIT 5 OFFSET 0
• This means that I need to keep the identifier of the last record from the previous batch.
• After completing the current batch, I need to wait 1 second (I think about using the PHP sleep() method) before starting a new batch.
• I need a loop
• Exact row count in TABLE_A is now unavailable
• Inserting new entries is simple. Focus on the iteration.

So, here is what I have on paper, and I'm not quite sure if this will work or not, because I really want to learn something from this problem. I will skip everything around, for example, to connect the database, etc., and focus only on the algorithm and write in some hybrid PHP / Mysql / Pseudo-code.

 $limit=5 $offset=0; function insert($limit, $offset){ $stm = $db->prepare("SELECT id FROM tableA LIMIT :limit OFFSET :offset"); $stm->execute(array('limit' => $limit, 'offset' => $offset)); while($stm->rowCount() > 0){ $data = $stm->fatchAll(); foreach($data as $row){ // insert into TABLE_B } sleep(1); $offset +=5; $this->insert($limit, $offset); } } 

I'm not quite sure if this recursion will work. On paper, this looks doable. But what about performance? Is this a problem in this case?

Perhaps the main question is: am I thinking about this? Do you know the best solution how to do this?

Thank you for any comments, thoughts, suggestions, ideas and detailed descriptions of your procedure, how to find a suitable solution. Probably I should do more work on the analysis and design of the algorithm. Do you know good resources?

(Sorry for grammar errors, I am not a native speaker)

Answer 1

I do not know why you need to insert 10 records in table B in 1 second, but suppose that this condition cannot be changed.

The source code is correct, however, recursion is not required here, we should do something similar.

 limit=5 offset=0 while (itemsA = fetch_from_a(limit, offset)) { # you should do a batch insertion here, see MySQL documentation. insert_into_B(itemsA); sleep(1); offset += 5; } # prototype # fetch some records from table A, return array of found items # or an empty array if nothing was found. function fetch_from_a(limit, offset);