Select a big number of insert in mysql

Question

I have a problem, so I have in my project 10 database. Each database have the table members. In each table members I have 2 millions of rows so in 10 databases ~ 20 millions of rows. I tried like this :

foreach ($aDataBases as $database) {
    $sSql = sprintf('SELECT nom,prenom,naiss FROM `%s`', $sTableName);
    $rResult = Mysqli::query($sSql, $database);
    while ($aRecord = $rResult->fetch_array(MYSQLI_ASSOC))
    {
        $aUsers['lastName']     = $aRecord['name'];
        $aUsers['firstName']    = $aRecord['f_name'];
        $aUsers['birthDate']    = $aRecord['birth'];
        $aTotalUsers[] = $aUsers;
    }
}

When I run I get the error Allowed memory size of 134217728 bytes exhausted. If for example I put in the select LIMIT 100 work perfect. Can you help me please ?

https://stackoverflow.com/questions/3534274/php-memory-exhausted see the first answer here — Nidhoegger, Nov 27 '15 at 08:20
I'm not familiar with php, but in java I would use an iterator for the result set and iterate the data. — heaphach, Nov 27 '15 at 08:21
I wouldn't even try reading that large a recordset into memory (an array). If you need to process that many records, then read each row in turn, process, read next, process, etc. That way, you're only working with one record in memory at a time — Mark Baker, Nov 27 '15 at 08:28
If you want to display it to a browser; don't..... don't try to present a web page displaying 20 million user records to a user...... they'll have terminated the request long before you can do so..... display 20 or 50 or 100 at a time using pagination — Mark Baker, Nov 27 '15 at 08:29

Amarnasan · Accepted Answer · 2015-11-27T08:22:56.307

Just put your code in a loop, and make SQL calls of, say, 1000 entries each. Loop until all rows have been printed. Some people will tell just to raise your memory limit, but there's always a physical limit you can't pass.

I won't code that for you because you're a PHP programmer and you got the idea. Here's the pseudocode, though:

base = 0
while (rows = getrows(base,1000)) 
  foreach row in rows
     print row
  base = base + 1000

Select a big number of insert in mysql

1 Answers1