Buffered and Unbuffered queries
Buffered and Unbuffered queries
Buffered and Unbuffered queries
Queries are using the buffered mode by default.
This means that query results are immediately transferred from the
MySQL Server to PHP and then are kept in the memory of the PHP
process. This allows additional operations like counting the number
of rows, and moving (seeking) the current result pointer. It also
allows issuing further queries on the same connection while working
on the result set. The downside of the buffered mode is that larger
result sets might require quite a lot memory. The memory will be
kept occupied till all references to the result set are unset or
the result set was explicitly freed, which will automatically
happen during request end the latest. The terminology “store
result” is also used for buffered mode, as the whole result set is
stored at once.
Note:
When using libmysqlclient as library PHP’s memory
limit won’t count the memory used for result sets unless the data
is fetched into PHP variables. With mysqlnd the memory accounted
for will include the full result set.
Unbuffered MySQL queries execute the query and then
return a resource while the data is still
waiting on the MySQL server for being fetched. This uses less
memory on the PHP-side, but can increase the load on the server.
Unless the full result set was fetched from the server no further
queries can be sent over the same connection. Unbuffered queries
can also be referred to as “use result”.
Following these characteristics buffered queries
should be used in cases where you expect only a limited result set
or need to know the amount of returned rows before reading all
rows. Unbuffered mode should be used when you expect larger
results.
Because buffered queries are the default, the
examples below will demonstrate how to execute unbuffered queries
with each API.
Example #1 Unbuffered query example: mysqli
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$uresult = $mysqli->query("SELECT Name FROM City", MYSQLI_USE_RESULT);
if (
$uresult) {
while ($row = $uresult->fetch_assoc()) {
echo $row['Name'] . PHP_EOL;
}
}
$uresult->close();
?>
Example #2 Unbuffered query example:
pdo_mysql
<?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;
}
}
?>
Example #3 Unbuffered query example: mysql
<?php
$conn = mysql_connect("localhost", "my_user", "my_pass");
$db = mysql_select_db("world");
$uresult = mysql_unbuffered_query("SELECT Name FROM City");
if ($uresult) {
while ($row = mysql_fetch_assoc($uresult)) {
echo $row['Name'] . PHP_EOL;
}
}
?>