mysqlinfo-php-concepts-php-buffering-4

  • Concepts
  • Buffered and Unbuffered queries

  • 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_QUERYfalse);

    $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;
       }
    }
    ?>