PHP Classes

PHP MySQL Buffered Query: Execute many queries of the same type all at once

Recommend this page to a friend!
  Info   View files Example   View files View files (8)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
StarStarStar 58%Total: 305 All time: 7,352 This week: 154Up
Version License PHP version Categories
buffered-query 1.0BSD License5.5PHP 5, Databases, Performance and opt...
Description 

Author

This package can execute many queries of the same type all at once.

There is a base class that can connect to a MySQL database using MySQLi and can queue multiple queries in a buffer to be executed later all at once using mysqli_multi_query.

There are sub-classes that can compose and queue queries of different types from parameters values.

Currently there are sub-classes for queueing INSERT, UPDATE and LOAD DATA INFILE queries.

Innovation Award
PHP Programming Innovation award nominee
March 2016
Number 7


Prize: One ebook of choice by Packt
MySQL can execute many queries at once with a single server call.

This class provides a means to execute multiple MySQL queries at once using a buffer to queue queries to be execute all at once within the same MySQL query access.

Manuel Lemos
Picture of Christian Vigh
  Performance   Level  
Name: Christian Vigh <contact>
Classes: 32 packages by
Country: France France
Age: 57
All time rank: 13810 in France France
Week rank: 10 Up1 in France France Up
Innovation award
Innovation award
Nominee: 20x

Winner: 3x

Example

<?php

   
/***
        To understand what happens here, I strongly suggest to consult the README.md file in this package !

        This example script performs the following :
        - Create one table, buffering_test, that we will be inserting/updating and loading data into
        - Time the insertion of MAX_ROWS rows using individual insert statements
        - Time the insertion of MAX_ROWS rows using a buffered insert object with a buffer size of MAX_INSERTS statements
        - Time the update of the rows created at the preceding step with individual UPDATE statements
        - Time the update of the rows created at the preceding step with a buffer size of MAX_UPDATES statements
        - Time the insertion of MAX_ROWS rows using a buffered load data object of MAX_INSERT rows

        Notes :
        - your database user MUST have the FILE privilege in order to use LOAD DATA INFILE statements
        - since the queries built by the BufferedInsert and BufferedUpdate classes may be very large, depending on
          the number of queries you wanted to buffer, you may have to increase the max_allowed_packet parameter in
          your my.cnf (unix) or my.ini (windows) file.
     ***/
   
require ( 'DbBufferedInsert.php' ) ;
    require (
'DbBufferedUpdate.php' ) ;
    require (
'DbBufferedLoadFile.php' ) ;

   
// Customize here the access parameters to your local database
   
define ( MYSQL_HOST , 'localhost' ) ;
   
define ( MYSQL_USER , 'root' ) ;
   
define ( MYSQL_PASSWORD , '' ) ;
   
define ( MYSQL_DATABASE , 'phpclasses' ) ;
   
define ( LOGFILE , 'data/example.log' ) ;

   
// String store entry types - one for the process name, one for the message part
   
define ( STRING_STORE_PROCESS , 0 ) ;
   
define ( STRING_STORE_MESSAGE , 1 ) ;

   
// Constants related to the size of our benchmark
   
define ( MAX_ROWS , 50000 ) ;
   
define ( MAX_INSERTS , 8192 ) ;
   
define ( MAX_UPDATES , 8192 ) ;
   
define ( MAX_LOAD_ROWS , 50000 ) ;

   
// Connect to your local database
   
$dblink = mysqli_connect ( MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD ) ;
   
$test_table = "buffering_test" ;

   
// Uncomment this if you want to create a brand new database for running this test
    /***
    $query = "CREATE DATABASE " . MYSQL_DATABASE . " DEFAULT CHARSET latin1" ;
    mysqli_query ( $dblink, $query ) ;
     ***/

    // Select our test database
   
mysqli_select_db ( $dblink, MYSQL_DATABASE ) ;

   
// Create the test table
   
$query = "
                CREATE TABLE IF NOT EXISTS
$test_table
                   (
                    id INT NOT NULL AUTO_INCREMENT,
                    date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
                    intvalue INT NOT NULL DEFAULT 0,
                    randvalue INT NOT NULL DEFAULT 0,
                    strvalue1 CHAR(32) NOT NULL DEFAULT '',
                    strvalue2 VARCHAR(4096) NOT NULL DEFAULT '',
                    strvalue3 LONGTEXT NOT NULL,

                    PRIMARY KEY ( id )
                    ) ENGINE = MyISAM
               "
;
   
mysqli_query ( $dblink, $query ) ;

   
// Time insertion in seconds.milliseconds of MAX_ROWS rows using individual INSERT statements
   
echo ( "Benchmarking buffered/unbuffered operations on " . MAX_ROWS . " rows :\n" ) ;

   
time_function ( 'IndividualInserts',
               
'Using individual INSERT statements',
               
$dblink, $test_table, MAX_ROWS ) ;

   
time_function ( 'BufferedInserts',
               
'Using buffered INSERT statements (size = ' . MAX_INSERTS . ')',
               
$dblink, $test_table, MAX_ROWS, MAX_INSERTS ) ;

   
time_function ( 'IndividualUpdates',
               
'Using individual UPDATE statements',
               
$dblink, $test_table, MAX_ROWS ) ;

   
time_function ( 'BufferedUpdates',
               
'Using buffered UPDATE statements (size = ' . MAX_UPDATES . ')',
               
$dblink, $test_table, MAX_ROWS, MAX_UPDATES ) ;

   
time_function ( 'BufferedLoads',
               
'Using buffered LOAD DATA INFILE statements (size = ' . MAX_LOAD_ROWS . ')',
               
$dblink, $test_table, MAX_ROWS, MAX_LOAD_ROWS ) ;

   
/*** END OF SCRIPT - the rest of this file contains the benchmarking functions ***/

    // time_function -
    // Times the execution of the specified function and display the results.
   
function time_function ( $funcname, $text, $dblink, $test_table, $max_rows, $buffer_size = null )
       {
        echo (
"\t" . str_pad ( $text, 60 ) . ' : ' ) ;
       
flush ( ) ;

       
$timer_start = microtime ( true ) ;
       
$funcname ( $dblink, $test_table, $max_rows, $buffer_size ) ;
       
$timer_stop = microtime ( true ) ;
       
$delta = round ( $timer_stop - $timer_start, 3 ) ;

       
mysqli_query ( $dblink, "OPTIMIZE TABLE $test_table" ) ;
       
mysqli_query ( $dblink, "FLUSH TABLES" ) ;

        echo (
$delta . "\n" ) ;
        }


   
// IndividualInserts -
    // Insert $row_count rows into the specified table using individual INSERT statements.
   
function IndividualInserts ( $dblink, $table_name, $row_count )
       {
       
mysqli_query ( $dblink, "TRUNCATE TABLE $table_name" ) ; // Make sure we start from a clean state

       
for ( $i = 1 ; $i <= $row_count ; $i ++ )
           {
           
$strvalue = sha1 ( microtime ( false ) ) ; // Well, we have to fill columns with some data...
           
$intvalue = mt_rand ( ) ;
           
$query = "
                        INSERT INTO
$table_name
                        SET
                            randvalue =
$intvalue,
                            date = NOW(),
                            intvalue =
$i,
                            strvalue1 = '
$strvalue',
                            strvalue2 = '
$strvalue',
                            strvalue3 = '
$strvalue'
                       "
;
           
mysqli_query ( $dblink, $query ) ;
            }
        }

   
// BufferedInserts -
    // Insert $row_count rows into the specified table using buffered INSERT statements.
   
function BufferedInserts ( $dblink, $table_name, $row_count, $buffer_size )
       {
       
mysqli_query ( $dblink, "TRUNCATE TABLE $table_name" ) ; // Make sure we start from a clean state
       
$buffer = new DbBufferedInsert ( $table_name, [ 'date', 'intvalue', 'randvalue', 'strvalue1', 'strvalue2', 'strvalue3' ], $buffer_size, $dblink ) ;

        for (
$i = 1 ; $i <= $row_count ; $i ++ )
           {
           
$strvalue = sha1 ( microtime ( true ) ) ; // Well, we have to fill columns with some data...
           
$intvalue = mt_rand ( ) ;
           
$buffer -> Add
              
([
               
'columns' =>
                   [
                   
'randvalue' => $intvalue,
                   
'intvalue' => $i,
                   
'strvalue1' => $strvalue,
                   
'strvalue2' => $strvalue,
                   
'strvalue3' => $strvalue
                   
],
               
'computed-columns' =>
                   [
                   
'date' => 'NOW()',
                    ]
                 ]) ;
            }

       
$buffer -> Flush ( ) ;
        }

   
// IndividualUpdates -
    // Udpates $row_count rows into the specified table using individual UPDATE statements.
    // The update consists of adding +1 to the intvalue column and an extra character to each string column.
    // The id field is used for identifying the row.
   
function IndividualUpdates ( $dblink, $table_name, $row_count )
       {
        for (
$i = 1 ; $i <= $row_count ; $i ++ )
           {
           
$query = "
                        UPDATE
$table_name
                        SET
                            randvalue = randvalue + 1,
                            strvalue1 = 'A
$i',
                            strvalue2 = 'B
$i',
                            strvalue3 = 'C
$i'
                        WHERE
                            id =
$i
                       "
;
           
mysqli_query ( $dblink, $query ) ;
            }
        }

   
// BufferedUpdates -
    // Updates $row_count rows into the specified table using buffered UPDATE statements.
   
function BufferedUpdates ( $dblink, $table_name, $row_count, $buffer_size )
       {
       
$buffer = new DbBufferedUpdate ( $table_name, [ 'id' ], [ 'intvalue', 'date', 'randvalue', 'strvalue1', 'strvalue2', 'strvalue3' ], $buffer_size, $dblink ) ;

        for (
$i = 1 ; $i <= $row_count ; $i ++ )
           {
           
$buffer -> Add
              
([
               
'keys' => [ 'id' => $i ],
               
'columns' =>
                   [
                   
'intvalue' => $i,
                   
'randvalue' => $i + 10000000,
                   
'strvalue1' => 'XXA' . $i,
                   
'strvalue2' => 'ZZB' . $i,
                   
'strvalue3' => 'ZZC' . $i
                   
],
               
'computed-columns' =>
                   [
                   
'date' => 'NOW()',
                    ]
                 ]) ;
            }

       
$buffer -> Flush ( ) ;
        }

   
// BufferedLoads -
    // Insert $row_count rows into the specified table using buffered LOAD DATA INFILE statements.
   
function BufferedLoads ( $dblink, $table_name, $row_count, $buffer_size )
       {
       
mysqli_query ( $dblink, "TRUNCATE TABLE $table_name" ) ; // Make sure we start from a clean state
       
$buffer = new DbBufferedLoadFile ( $table_name, [ 'intvalue', 'randvalue', 'strvalue1', 'strvalue2', 'strvalue3' ], $buffer_size, $dblink ) ;

        for (
$i = 1 ; $i <= $row_count ; $i ++ )
           {
           
$strvalue = sha1 ( microtime ( true ) ) ; // Well, we have to fill columns with some data...
           
$intvalue = mt_rand ( ) ;
           
$buffer -> Add
              
([
               
'columns' =>
                   [
                   
'randvalue' => $intvalue,
                   
'intvalue' => $i,
                   
'strvalue1' => $strvalue,
                   
'strvalue2' => $strvalue,
                   
'strvalue3' => $strvalue
                   
]
                 ]) ;
            }

       
$buffer -> Flush ( ) ;
        }


  Files folder image Files  
File Role Description
Accessible without login Plain text file benchmark.php Example Benchmark for DbBufferedOperation classes
Plain text file DbBufferedInsert.php Class DbBufferedInsert class source
Plain text file DbBufferedLoadFile.php Class DbBufferedLoadFile class source
Plain text file DbBufferedOperation.php Class Base class for all other buffered operation classes
Plain text file DbBufferedUpdate.php Class DbBufferedUpdate class source
Accessible without login Plain text file LICENSE Lic. License file
Accessible without login Plain text file NOTICE Data Disclaimer
Accessible without login Plain text file README.md Doc. Full help

 Version Control Unique User Downloads Download Rankings  
 0%
Total:305
This week:0
All time:7,352
This week:154Up
 User Ratings  
 
 All time
Utility:75%StarStarStarStar
Consistency:60%StarStarStarStar
Documentation:75%StarStarStarStar
Examples:75%StarStarStarStar
Tests:-
Videos:-
Overall:58%StarStarStar
Rank:1537