How can I split this INSERT into steps?

Asked

Viewed 353 times

1

I have 70MIL records to insert into one Tabela3 from Tabela1 - 4 fields and table 2 - 1 field. The following code does exactly what I need the most optimized way a user has helped me build.

INSERT INTO imagens3 (ID, IMOVEL, CODIGO, IMAGEM_G, IMAGEM_P) 
SELECT t1.ID, t1.IMOVEL, t1.CODIGO, t2.IMAGEM_G, t1.IMAGEM_P 
FROM imagens1 t1 INNER JOIN imagens2 t2 ON t1.ID = t2.ID LIMIT 10000

I need to insert 10MIL at a time to get to the end of my work successfully.

How I can create an instruction to insert 10MIL records at a time through php?

php mysql query

  • I don’t work with php, but can you work with a list? Because then you could use the IN command in your mysql, and pass part of that list by execution blocks. NOTE: The IN command accepts up to 2100 records. I hope I helped. Otherwise, comment so we can analyze better.

  • 2100 records is little. This will not solve my problem.

  • 1

    Yes, I know, I had this same problem these days but in Java, and I had to do one for every 2100 records to do the insertion. In my case, it was worth doing as a block for the performance, but this in Java, I don’t work with PHP, so I didn’t post as an answer, but maybe it’s an option.

3 answers

3

You can use the two parameters allowed in the clause LIMIT, where the first indicates from which line you want to read (indexed at zero).

Thus, the first query remains the same, rescuing up to 10,000 records in the source table (imagens1) to be inserted into the target table (imagens3):

... FROM imagens1 t1 INNER JOIN imagens2 t2 ON t1.ID = t2.ID LIMIT 10000

In the second run, you enter two parameters for LIMIT, indicating that you want to return another 10,000 records from the 10,000 line (which was not covered by the previous INSERT).

... FROM imagens1 t1 INNER JOIN imagens2 t2 ON t1.ID = t2.ID LIMIT 10000, 10000

Now you’ve got the logic; proceed until you’ve rescued and entered the 70,000 lines:

... FROM imagens1 t1 INNER JOIN imagens2 t2 ON t1.ID = t2.ID LIMIT 20000, 10000
... 
... FROM imagens1 t1 INNER JOIN imagens2 t2 ON t1.ID = t2.ID LIMIT 30000, 10000
...

  • The problem is that this number is going to vary all the time, it could be 70, 80, 90,000, so I needed this to be dynamic. Can you help me a little more?

  • @Marcosvinicius All you need to do is write a PHP loop that alters the LIMIT parameters for each run. If you need to copy 90,000 records, run the loop 9 times by switching the first parameter.

3

In PHP, regardless of the amount of records in the database:

<?
mysql_connect( ... );
$query = mysql_query("SELECT * FROM imagens1 t1 INNER JOIN imagens2 t2 ON t1.ID = t2.ID");
$num_rows = mysql_num_rows($query);

for($i = 0, $i < $num_rows; $i += 10000) {
  mysql_query(
    "INSERT INTO imagens3 (ID, IMOVEL, CODIGO, IMAGEM_G, IMAGEM_P) 
     SELECT t1.ID, t1.IMOVEL, t1.CODIGO, t2.IMAGEM_G, t1.IMAGEM_P 
     FROM imagens1 t1 INNER JOIN imagens2 t2 ON t1.ID = t2.ID LIMIT 10000 OFFSET #{i}");
}

  • The amount of images is dynamic, can help me with PHP so that it pulls as long as there are images please?

  • 1

    @Marcosvinicius ready, see if it’s cool

  • To get the number of lines was a little long but I’ll give a small and I’ll test the code. I’ll tell you what.

  • 1

    Nice solution. @Marcosvinicius, to get the number of rows you can replace the SELECT columns with count(*) or count(t1.ID). Thus: "SELECT count(*) FROM imagens1 t1 INNER JOIN imagens2 t2 ON t1.ID = t2.ID");. The query gets smaller and also faster.

  • Is giving an error this query: Will it is there where is the SHARP?

  • SHARP? I don’t understand

Show 1 more comment

3

As stated in other answers, it is possible through the OFFSET:

$inicio = 0;
$limite = 10000;
$total = 70000;

$sql = "INSERT INTO imagens3 (ID, IMOVEL, CODIGO, IMAGEM_G, IMAGEM_P) "
. "SELECT t1.ID, t1.IMOVEL, t1.CODIGO, t2.IMAGEM_G, t1.IMAGEM_P "
. "FROM imagens1 t1 INNER JOIN imagens2 t2 ON t1.ID = t2.ID LIMIT ";

for ($inicio; $inicio <= $total; $inicio += $limite) {
    echo $sql . "{$inicio}, {$limite}\n";
}

See example working on ideone.

Browser other questions tagged php mysql query

You are not signed in. Login or sign up in order to post.