Difference Between Buffered and Unbuffered Queries

Difference between buffered and unbuffered queries

See: http://php.net/manual/en/mysqlinfo.concepts.buffering.php

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.

Buffered queries are default.

Unbuffered Example:

<?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();
?>

mysqli prepared statements buffered data vs unbuffered data Performance?

Although I haven't ever had a need for stored results I still found this question interesting and would like to know the same thing. I have created a database with junk data in it and ran some tests against it to see timing differences using unbuffered and buffered results. I've included my tests and findings in case it helps you (or anyone else for that matter).

Database Setup

The database consists of a single table with four different fields. Here is the schema:

ID          | int(5)    | primary_key, auto_increment
Name        | tinytext  | not_null
CountryCode | int(3)    | not_null
Description | tinytext  | not_null

The table has over 10,000 rows all with junk data. ID is filled with its auto-increment value, CountryCode is a random number between 11 and 999, and the last two fields, Name and Description, contain md5 hashes of numerical data.

The Tests

In total six different tests are done. The first 5 tests are comparisons of processing times between unbuffered and buffered results. The overall goal of the tests are to see benchmarks of different sized results sets starting at the beginning of the table and in the middle. The final test is only a benchmark of buffered results being accessed randomly versus sequentially. Unbuffered results cannot be arbitrarily through mysqli_stmt_data_seek() so a comparison would be difficult to craft and be considered fair.

Benchmark times are calculated using microtime(). Ticks are started right before the MySQLi statement is prepared and ended as soon as the statement is closed.

Here is a breakdown of the 6 tests:

Test 1: Compares unbuffered/buffered processing times of a result set of 100 rows selected from the beginning of the table.
Test 2: Compares unbuffered/buffered processing times of a result set of 100 rows selected from the middle of the table.
Test 3: Compares unbuffered/buffered processing times of a result set of 1000 rows selected from the beginning of the table.
Test 4: Compares unbuffered/buffered processing times of a result set of 1000 rows selected from the middle of the table.
Test 5: Compares unbuffered/buffered processing times of a result set of 5000 rows selected from the beginning of the table and repeated three times.
Test 6: Benchmark of the processing time of a result set of 10000 rows, accessed randomly, and repeated three times.

The Results

PHP produced the following output for the tests described above.

Test 1
Took 0.002000093460083 seconds to process unbuffered result of 100 rows from the beginning of the table
Took 0.0019998550415039 seconds to process buffered result of 100 rows from the beginning of the table

Test 2
Took 0.012001037597656 seconds to process unbuffered result of 100 rows from the middle of the table
Took 0.011001110076904 seconds to process buffered result of 100 rows from the middle of the table

Test 3
Took 0.013001918792725 seconds to process unbuffered result of 1000 rows from the beginning of the table
Took 0.012001037597656 seconds to process buffered result of 1000 rows from the beginning of the table

Test 4
Took 0.023001909255981 seconds to process unbuffered result of 1000 rows from the middle of the table
Took 0.020002126693726 seconds to process buffered result of 1000 rows from the middle of the table

Test 5
Took 0.19601988792419 seconds to process unbuffered result of 5000 rows sequentially, three times
Took 0.085008144378662 seconds to process buffered result of 5000 rows sequentially, three times

Test 6
Took 4.2634270191193 seconds to process buffered result of 10000 rows randomly, three times

Conclusion

Tests 1-4 show that the performance gain is negligible for the most part. Gains from buffering the results are minimal even when dealing with a lot of rows or taking records from various offsets in the table. Offsetting the location added some overhead (about a hundredth of second to advance 5000 rows).

That said, buffering still has its place. In test five, where a result set with several thousand rows is iterated multiple times, there is a clear benefit from using buffered results. Both the buffered and unbuffered versions of that test effectively process 15,000 rows. But because the buffered version does not have to retrieve the results again it is able to do its work in less than half of the time the unbuffered version does.

As pointed out already by others in this question, buffering is very helpful when having to access rows arbitrarily/randomly. Test six simply shows how long a buffered set of 10,000 rows can be accessed randomly and then repeated an additional two times. Test six effectively fetches 30,000 rows completely out of order.

Of Course, The Code

Here is the code I used to create this test. It is all procedural so it isn't the prettiest to look at, but if I ever find myself making a class out of this or modifying the code to clean it up, I'll be sure to update it here!

<?php

//tell PHP not to mind how long it is running
set_time_limit(0);
//control output for test results
ob_start();
//array to hold time values from the tests
$times = array();

//Connect to the database
$connection = mysqli_connect("localhost", "root", "", "blah");

/***********************************************************************
 * TEST 1: Small result set of 100 rows from the beginning of the table
 **********************************************************************/

$times['Test 1'] = array();

//UNBUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 100");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);

while (mysqli_stmt_fetch($stmt))
{
    printf("%d, %s, %d, %s \n", $id, $name, $code, $desc);
}
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);

$time = $benchmarkEnd - $benchmarkStart;
$times['Test 1'][] = "Took $time seconds to process unbuffered result of 100 rows from the beginning of the table";

//BUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 100");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);
mysqli_stmt_store_result($stmt);

while (mysqli_stmt_fetch($stmt))
{
    printf("%d, %s, %d, %s \n", $id, $name, $code, $desc);
}
mysqli_stmt_free_result($stmt);
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);

$time = $benchmarkEnd - $benchmarkStart;
$times['Test 1'][] = "Took $time seconds to process buffered result of 100 rows from the beginning of the table";
echo "\n ** END TEST 1** \n\n";

/*******************************************************************
 * TEST 2: Small result set of 100 rows from the middle of the table
 ******************************************************************/

$times['Test 2'] = array();

//UNBUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 5000, 100");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);

while (mysqli_stmt_fetch($stmt))
{
    printf("%d, %s, %d, %s \n", $id, $name, $code, $desc);
}
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);

$time = $benchmarkEnd - $benchmarkStart;
$times['Test 2'][] = "Took $time seconds to process unbuffered result of 100 rows from the middle of the table";

//BUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 5000, 100");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);
mysqli_stmt_store_result($stmt);

while (mysqli_stmt_fetch($stmt))
{
    printf("%d, %s, %d, %s \n", $id, $name, $code, $desc);
}
mysqli_stmt_free_result($stmt);
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);

$time = $benchmarkEnd - $benchmarkStart;
$times['Test 2'][] = "Took $time seconds to process buffered result of 100 rows from the middle of the table";
echo "\n ** END TEST 2** \n\n";

/***********************************************************************
 * TEST 3: Large result set of 1000 rows from the beginning of the table
 **********************************************************************/

$times['Test 3'] = array();

//UNBUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 1000");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);

while (mysqli_stmt_fetch($stmt))
{
    printf("%d, %s, %d, %s \n", $id, $name, $code, $desc);
}
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);

$time = $benchmarkEnd - $benchmarkStart;
$times['Test 3'][] = "Took $time seconds to process unbuffered result of 1000 rows from the beginning of the table";

//BUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 1000");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);
mysqli_stmt_store_result($stmt);

while (mysqli_stmt_fetch($stmt))
{
    printf("%d, %s, %d, %s \n", $id, $name, $code, $desc);
}
mysqli_stmt_free_result($stmt);
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);

$time = $benchmarkEnd - $benchmarkStart;
$times['Test 3'][] = "Took $time seconds to process buffered result of 1000 rows from the beginning of the table";
echo "\n ** END TEST 3** \n\n";

/********************************************************************
 * TEST 4: Large result set of 1000 rows from the middle of the table
 *******************************************************************/

$times['Test 4'] = array();

//UNBUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 5000, 1000");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);

while (mysqli_stmt_fetch($stmt))
{
    printf("%d, %s, %d, %s \n", $id, $name, $code, $desc);
}
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);

$time = $benchmarkEnd - $benchmarkStart;
$times['Test 4'][] = "Took $time seconds to process unbuffered result of 1000 rows from the middle of the table";

//BUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 5000, 1000");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);
mysqli_stmt_store_result($stmt);

while (mysqli_stmt_fetch($stmt))
{
    printf("%d, %s, %d, %s \n", $id, $name, $code, $desc);
}
mysqli_stmt_free_result($stmt);
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);

$time = $benchmarkEnd - $benchmarkStart;
$times['Test 4'][] = "Took $time seconds to process buffered result of 1000 rows from the middle of the table";
echo "\n ** END TEST 4** \n\n";

/******************************************************************************
 * TEST 5: Work with larger result set, 5000 rows, multiple times, sequentially
 *****************************************************************************/

$times['Test 5'] = array();

//UNBUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 5000");

for ($i = 0; $i < 3; $i++)
{
    mysqli_stmt_execute($stmt);
    mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);
    while (mysqli_stmt_fetch($stmt))
    {
        printf("%d, %s, %d, %s \n", $id, $name, $code, $desc);
    }
}
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);

$time = $benchmarkEnd - $benchmarkStart;
$times['Test 5'][] = "Took $time seconds to process unbuffered result of 5000 rows sequentially, three times";

//BUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 5000");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);
mysqli_stmt_store_result($stmt);

for ($i = 0; $i < 3; $i++)
{
    mysqli_stmt_data_seek($stmt, 0);
    while (mysqli_stmt_fetch($stmt))
    {
        printf("%d, %s, %d, %s \n", $id, $name, $code, $desc);
    }
}
mysqli_stmt_free_result($stmt);
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);

$time = $benchmarkEnd - $benchmarkStart;
$times['Test 5'][] = "Took $time seconds to process buffered result of 5000 rows sequentially, three times";
echo "\n ** END TEST 5** \n\n";

/***************************************************************************
 * TEST 6: Work with larger result set, 10000 rows, multiple times, randomly
 **************************************************************************/

$times['Test 6'] = array();

//UNBUFFERED VERSION
//Can't test unbuffered result sets randomly as mysqli_stmt_data_seek
//only works on buffered results.

//BUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 10000");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);
mysqli_stmt_store_result($stmt);

for ($i = 0; $i < 3; $i++)
{
    $rows = range(0, (mysqli_stmt_num_rows($stmt) - 1));
    shuffle($rows);
    for ($j = 0; $j < 10000; $j++)
    {
        mysqli_stmt_fetch($stmt);
        printf("%d, %s, %d, %s \n", $id, $name, $code, $desc);
        $row = $rows[0];
        mysqli_stmt_data_seek($stmt, $row);
        array_shift($rows);
    }
}
mysqli_stmt_free_result($stmt);
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);

$time = $benchmarkEnd - $benchmarkStart;
$times['Test 6'][] = "Took $time seconds to process buffered result of 10000 rows randomly, three times";
echo "\n ** END TEST 6** \n\n";

/*******************
 * Print the results
 ******************/
$output = ob_get_clean();
foreach ($times as $tests => $results)
{
    echo $tests . "\n";
    foreach ($results as $result)
    {
        echo $result . "\n";
    }
    echo "\n\n";
}

//Dumps all of those rows that have been getting printed out to the browser.
//This kicked out a little north of 64,000 lines in my browser.
echo $output; 

?>

PHP PDO Buffered query problem

If I understand this right, buffered queries involve telling PHP that you want to wait for the entire result set before you begin processing. Prior to PDO, this was the default and you had to call mysql_unbuffered_query if you wanted to deal with results immediately.

Why this isn't explained on the PDO MySQL driver page, I don't know.

Do unbuffered queries for one request

You can set the attribute on the PDO connection:

$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

then run this particular query which result needs to be unbuffered,

$uresult = $pdo->query("SELECT Name FROM City");
while ($row = $uresult->fetch(PDO::FETCH_ASSOC)) {
    echo $row['Name'] . PHP_EOL;
}

and then set the attribute back

$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

what is a mysql buffered cursor w.r.t python mysql connector

I can think of two ways these two types of Cursors are different.

The first way is that if you execute a query using a buffered cursor, you can get the number of rows returned by checking MySQLCursorBuffered.rowcount. However, the rowcount attribute of an unbuffered cursor returns -1 right after the execute method is called. This, basically, means that the entire result set has not yet been fetched from the server. Furthermore, the rowcount attribute of an unbuffered cursor increases as you fetch rows from it, while the rowcount attribute of a buffered cursor remains the same, as you fetch rows from it.

The following snippet code tries to illustrate the points made above:

import mysql.connector

conn = mysql.connector.connect(database='db',
                               user='username',
                               password='pass',
                               host='localhost',
                               port=3306)

buffered_cursor = conn.cursor(buffered=True)
unbuffered_cursor = conn.cursor(buffered=False)

create_query = """
drop table if exists people;
create table if not exists people (
    personid int(10) unsigned auto_increment,
    firstname varchar(255),
    lastname varchar(255),
    primary key (personid)
);
insert into people (firstname, lastname)
values ('Jon', 'Bon Jovi'),
('David', 'Bryan'),
('Tico', 'Torres'),
('Phil', 'Xenidis'),
('Hugh', 'McDonald')
"""

# Create and populate a table
results = buffered_cursor.execute(create_query, multi=True)
conn.commit()

buffered_cursor.execute("select * from people")
print("Row count from a buffer cursor:", buffered_cursor.rowcount)
unbuffered_cursor.execute("select * from people")
print("Row count from an unbuffered cursor:", unbuffered_cursor.rowcount)

print()
print("Fetching rows from a buffered cursor: ")

while True:
    try:
        row = next(buffered_cursor)
        print("Row:", row)
        print("Row count:", buffered_cursor.rowcount)
    except StopIteration:
        break

print()
print("Fetching rows from an unbuffered cursor: ")

while True:
    try:
        row = next(unbuffered_cursor)
        print("Row:", row)
        print("Row count:", unbuffered_cursor.rowcount)
    except StopIteration:
        break

The above snippet should return something like the following:

Row count from a buffered reader:  5
Row count from an unbuffered reader:  -1

Fetching rows from a buffered cursor:
Row: (1, 'Jon', 'Bon Jovi')
Row count: 5
Row: (2, 'David', 'Bryan')
Row count: 5
Row: (3, 'Tico', 'Torres')
Row count: 5
Row: (4, 'Phil', 'Xenidis')
Row count: 5
Row: (5, 'Hugh', 'McDonald')
Row: 5

Fetching rows from an unbuffered cursor:
Row: (1, 'Jon', 'Bon Jovi')
Row count: 1
Row: (2, 'David', 'Bryan')
Row count: 2
Row: (3, 'Tico', 'Torres')
Row count: 3
Row: (4, 'Phil', 'Xenidis')
Row count: 4
Row: (5, 'Hugh', 'McDonald')
Row count: 5

As you can see, the rowcount attribute for the unbuffered cursor starts at -1 and increases as we loop through the result it generates. This is not the case with the buffered cursor.

The second way to tell the difference is by paying attention to which of the two (under the same connection) executes first. If you start with executing an unbuffered cursor whose rows have not been fully fetched and then try to execute a query with the buffered cursor, an InternalError exception will be raised, and you will be asked to consume or ditch what is returned by the unbuffered cursor. Below is an illustration:

import mysql.connector

conn = mysql.connector.connect(database='db',
                               user='username',
                               password='pass',
                               host='localhost',
                               port=3306)

buffered_cursor = conn.cursor(buffered=True)
unbuffered_cursor = conn.cursor(buffered=False)

create_query = """
drop table if exists people;
create table if not exists people (
    personid int(10) unsigned auto_increment,
    firstname varchar(255),
    lastname varchar(255),
    primary key (personid)
);
insert into people (firstname, lastname)
values ('Jon', 'Bon Jovi'),
('David', 'Bryan'),
('Tico', 'Torres'),
('Phil', 'Xenidis'),
('Hugh', 'McDonald')
"""

# Create and populate a table
results = buffered_cursor.execute(create_query, multi=True)
conn.commit()

unbuffered_cursor.execute("select * from people")
unbuffered_cursor.fetchone()
buffered_cursor.execute("select * from people")

The snippet above will raise a InternalError exception with a message indicating that there is some unread result. What it is basically saying is that the result returned by the unbuffered cursor needs to be fully consumed before you can execute another query with any cursor under the same connection. If you change unbuffered_cursor.fetchone() with unbuffered_cursor.fetchall(), the error will disappear.

There are other less obvious differences, such as memory consumption. Buffered cursor will likely consume more memory since they may fetch the result set from the server and buffer the rows.

I hope this proves useful.

PDO failing with too many records, buffered queries

When you use an unbuffered query, that means your result set is being streamed in from the MySQL server. So, the connection on which the (unbuffered) query runs is busy until you read the last row of the query. In your case the connection is $MysqlConn.

(A buffered query slurps the entire resultset into your php program's RAM and frees up the connection. You use unbuffered queries when your whole result set doesn't fit in RAM).

Unbuffered queries should be closed explicitly when you're done with them. So add a closeCursor() call. Like this.

while ($row = $ordStat->fetch(PDO::FETCH_ASSOC)) {
    $order_ids[] = $row['order_id'];
}
$ordStat->closeCursor();

There's no harm in closing buffered queries too. It's a good habit.

Difference Between Buffered and Unbuffered Queries