script optimization (Fatal error: Allowed memory)

Obviously, a large amount of data requires a large amount of memory when the method works. For minimal memory usage on the client side, the cursors are used relative to the base, you use it with the mysql_fetch_array function, which retrieves the next result from the cursor.

When using the mysql_query function, it buffers the result, you work with the data cursor that is processed and stored in the PHP script memory, naturally with a large amount of data, the usual amount of memory allocated for the script is not enough. This is called a buffered query.

The mysql_unbuffered_query function comes to the rescue, it does not save the result of execution into memory, but moves the cursor in the database, making the script's memory not clogged. This is called an unbuffered request.

That is, in the case of mysql_query, you work with a cursor from PHP memory, and in the second case with a database cursor. Change mysql_query to mysql_unbuffered_query and everything will be fine.

I want to note that the mysql extension is outdated and its use is insecure, at least due to the lack of parameter binding functions, and lack of support in PHP 5.5 and higher, use the mysqli or PDO library, the latter provides more convenient object-oriented data access.

Unbuffered queries have advantages and disadvantages:

Benefits:

1. The result can be started to read earlier, the waiting time is reduced;
2. The result does not take place in the RAM.

Disadvantages:

1. It is impossible to know how many rows are received;
2. It is impossible to move to a specific result, that is, you can only read data from the beginning and in order;
3. No other queries can be performed until this result is closed.

An unbuffered PDO request looks like this:

 <?php $pdo = new PDO("mysql:host=localhost;dbname=world", 'my_user', 'my_pass'); $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); $uresult = $pdo->query("SELECT Name FROM City"); if ($uresult) { while ($row = $uresult->fetch(PDO::FETCH_ASSOC)) { echo $row['Name'] . PHP_EOL; } } ?> 

On the official PHP site there is information on a buffered and unbuffered query in English.

PS If you only need an ID, select only id in the query, but not all fields from the table

To optimize the memory handling of a large table bypassing task, select not the entire table, but only the column with the primary key from it:

 /*Обход большой таблицы с экономией памяти*/ $query = mysql_query('SELECT id FROM `table`;'); while ($id = mysql_fetch_array($query)[0]) { $row = mysql_fetch_array( mysql_query('SELECT * FROM `table` where id = '.$id.';') ); /* Делаем что-то с полными данными ряда таблицы $row */ ... } 

Inside the loop, if the task requires it - select the complete data for the series, knowing id. In this case, each step of the cycle, the memory captured by the $row variable will go to the garbage collector, i.e. will be released if necessary.

By the way, this is a dogma when writing PHP porting scripts. Dogma this truth refers to query builders - which do not distinguish between query and fetch in the general case to simplify work with the database.

I note that the mysql driver is also vulnerable, what is written on every page of php.net dedicated to it. You must use PDO , Doctrine DBAL , or at worst mysqli .