Performance tuning with MDB2

<?php
 
// PEAR error handling setup
require_once 'PEAR.php';
function pearError ($e)
{
  echo '<pre>';
  echo $e->getMessage().': '.$e->getUserinfo();
  echo '</pre>';
}
PEAR::setErrorHandling(
  PEAR_ERROR_CALLBACK,
  'pearError'
);
 
// creating MDB2 instance
require_once 'MDB2.php';
$dsn = 'mysql://root:test@localhost';
$mdb2 =& MDB2::factory($dsn);
$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);
 
// The custom error handler
//
// It will collect all the queries being executed
// in the script, the collection is done by the
// collectInfo() method.
// Once the script finishes executing, we'll call
// the method executeAndExplain() which will
// execute all unique SELECTs once again
// in order to give us an info of how much time
// each query takes.
// Then executeAndExplain() will execute again
// all SELECTs, this time prepending an EXPLAIN
// so that we can get valuable
// optimization-related information
// Not only that but instead of simple EXPLAIN,
// we can use EXPLAIN EXTENDED and after that
// we can call SHOW WARNINGS -
// this will give us even more optimization hints
//
// http://dev.mysql.com/doc/refman/5.1/en/explain.html
// http://dev.mysql.com/doc/refman/5.1/en/show-warnings.html
//
class Explain_Queries
{
  // how many queries were executed
  var $query_count = 0;
  // which queries and their count
  var $queries = array();
  // results of EXPLAIN-ed SELECTs
  var $explains = array();
  // the MDB2 instance
  var $db = false;
 
  // constructor that accepts MDB2 reference
  function Explain_Queries(&$db) {
    $this->db = $db;
  }
 
  // this method is called on every query
  function collectInfo(
    &$db,
    $scope,
    $message,
    $is_manip = null)
  {
    // increment the total number of queries
    $this->query_count++;
    // the SQL is a key in the queries array
    // the value will be the count of how
    // many times each query was executed
    @$this->queries[$message]++;
  }
 
  // print the debug information
  function dumpInfo()
  {
    echo '<h3>Queries on this page</h3>';
    echo '<pre>';
    print_r($this->queries);
    echo '</pre>';
    echo '<h3>EXPLAIN-ed SELECTs</h3>';
    echo '<pre>';
    print_r($this->explains);
    echo '</pre>';
  }
 
  // the method that will execute all SELECTs
  // with and without an EXPLAIN and will
  // create $this->explains array of debug
  // information
  // SHOW WARNINGS will be called after each
  // EXPLAIN for more information
  function executeAndExplain() {
 
    // at this point, stop debugging
    $this->db->setOption('debug', 0);
    $this->db->loadModule('Extended');
 
    // take the SQL for all the unique queries
    $queries = array_keys($this->queries);
    foreach ($queries AS $sql) {
 
      // for all SELECTs…
      $sql = trim($sql);
      if (stristr($sql,"SELECT") !== false){
        // note the start time
        $start_time = array_sum(
            explode(" ", microtime())
        );
        // execute query
        $this->db->query($sql);
        // note the end time
        $end_time = array_sum(
            explode(" ", microtime())
        );
        // the time the query took
        $total_time = $end_time - $start_time;
 
        // now execute the same query with
        // EXPLAIN EXTENDED prepended
        $explain = $this->db->getAll(
          'EXPLAIN EXTENDED ' . $sql
        );
 
        $this->explains[$sql] = array();
        // update the debug array with the
        // new data from
        // EXPLAIN and SHOW WARNINGS
        if (!PEAR::isError($explain)) {
          $this->explains[$sql]['explain'] = $explain;
          $this->explains[$sql]['warnings'] =
               $this->db->getAll('SHOW WARNINGS');
        }
 
        // update the debug array with the
        // count and time
        $this->explains[$sql]['time'] = $total_time;
      }
    }
  }
}
 
// instance of the custom debug handler
$my_debug_handler = new Explain_Queries($mdb2);
// set debug option
$mdb2->setOption('debug', 1);
// set debug handler to the method that
// collects all queries
$mdb2->setOption(
  'debug_handler',
  array($my_debug_handler, 'collectInfo')
);
// register functions to be executed on shut down
// after the script has finished execution.
// Now that the show's over, it's the time to
// report what happened in this script db-access-wise
// First shutdown function executes the
// SELECTs again, the other one prints the results
register_shutdown_function(
  array($my_debug_handler, 'executeAndExplain')
);
register_shutdown_function(
  array($my_debug_handler, 'dumpInfo')
);
 
 
//
//
// At this point all MDB2 setup is done,
// time for the actual script to do something
//
//
 
// load the DB manager module
$mdb2->loadModule('Manager');
 
// drop database if it exists
// temporarily change the PEAR error handling
PEAR::pushErrorHandling(PEAR_ERROR_RETURN);
$mdb2->dropDatabase('test_db_explain');
PEAR::popErrorHandling();
 
// create and set a new database
$mdb2->createDatabase('test_db_explain');
$mdb2->setDatabase('test_db_explain');
 
// create table "events" from a definition array
// the table has event ID, name and date/time
$definition = array (
  'id' => array (
    'type' => 'integer',
    'unsigned' => 1,
    'notnull' => 1,
    'default' => 0,
  ),
  'name' => array (
    'type' => 'text',
    'length' => 255
  ),
  'datetime' => array (
    'type' => 'timestamp'
  )
);
 
$mdb2->createTable('events', $definition);
 
// create a primary key - the ID field
$definition = array (
  'primary' => true,
  'fields' => array (
    'id' => array()
  )
);
$mdb2->createConstraint(
  'events',
  'myprimekey',
  $definition
);
 
// load the class that has some static helper
// functions to work with MDB2's cross-RDBMS
// date format
MDB2::loadFile('Date');
 
// INSERT
// some data to insert into the events table
$data = array(
  // using MDB2-managed sequences
  'id'     => $mdb2->nextId('events'),
  'name'     => "Breakfast a Tiffany's",
  'datetime'   => MDB2_Date::unix2Mdbstamp(
    strtotime('Jan 15, 2007')
  )
);
// The "datetime" value shows how you can use
// any date format you wish as long as you're
// able to get a unix timestamp out of it
// In this case I'm using strtotime()
// Then there is a call to MDB2's date helper
// to get the MDB2 timestamp
 
// auto insert
// for the autoExecute() method we need to
// load the Extended module
$mdb2->loadModule('Extended');
$result = $mdb2->autoExecute(
  'events',
  $data,
  MDB2_AUTOQUERY_INSERT
);
 
//
// Time to SELECT something
//
// Using date helpers again
$start_date = MDB2_Date::date2Mdbstamp(0,0,0,12,31,1980);
$end_date   = MDB2_Date::date2Mdbstamp(0,0,0,12,31,2020);
$sql = 'SELECT * FROM %s WHERE %s > %s AND %s < %s';
$sql = sprintf(
  $sql,
  $mdb2->quoteIdentifier('events'),   // quote table name
  $mdb2->quoteIdentifier('datetime'), // quote field name
  $mdb2->quote($start_date, 'date'),  // quote data as date
  $mdb2->quoteIdentifier('datetime'), // quote field name
  $mdb2->quote($end_date,   'date')   // quote data as date
);
$res = $mdb2->getAll($sql); // execute
 
//
// * Bad practice code follows *
// Just some more inserts and selects, bad
// practice because these queries are not
// necessarily portable accross various RDBMS,
// lacking proper quoting and preparation
for ($i = 2; $i < 31; $i++) {
  $mdb2->query(
    'INSERT INTO events VALUES('
    . $i
    . ', "test event", "2005-05-05 00:00:00")'
  );
}
$res = $mdb2->getCol(
  'SELECT DISTINCT datetime FROM events'
);
$res = $mdb2->getRow(
  'SELECT * FROM events WHERE id
  IN (SELECT id FROM events WHERE id > 1)'
);
?>