How do I make my PDO connection more efficient?

Question

Okay, I think I should start by saying that the snippet bellow is my end goal. I want to be able to run a line like this anywhere in my application and have it handle the PDO connection and execution:

Database::query('QUERY');

To do this, I have the following two files running in the background.

Connection.php: handles the PDO connection.

class Connection {

    /**
     * The PDO connection.
     *
     * @var PDO
     */
    protected $pdo = NULL;

    /**
     * The type of database we're connection to.
     *
     * @var string
     */
    protected $type = '';

    /**
     * The name of the connected database.
     *
     * @var string
     */
    protected $database = '';

    /**
     * The database connection details.
     *
     * @var array
     */
    protected $config = array();

    /**
     * Create the connection instance.
     *
     */
    public function __construct()
    {
        // Import the configuration information from database.php
        $this->config = Config::load('database');

        // Pull the database type
        $this->type = $this->config['database'];

        // Pull the database name
        if(isset($this->config[$this->type]['database']))
        {
            $this->database = $this->config[$this->type]['database'];   
        }

        // Check to see if a connection has been made or not
        if($this->pdo==NULL)
        {
            // Create the connection
            $this->pdo = $this->createConnection();
        }   
    }

    /**
     * Query the database.
     *
     * @param string $query
     * @return array
     */
    public function query($query)
    {
        // Check to see if we have a connection
        if($this->pdo!=NULL)
        {
            // Execute the raw query
            $query = $this->pdo->query($query);

            return $query;
        }

        return false;
    }

    /**
     * Execute a query on the database.
     *
     * @param string $query
     * @return int
     */
    public function exec($query)
    {
        // Check to see if we have a connection
        if($this->pdo!=NULL)
        {
            // Execute the raw query
            $execution = $this->pdo->exec($query);

            return $execution;
        }

        return false;
    }

    /**
     * Execute a query and return the last inserted Id
     *
     * @param string $query
     * @return int
     */
    public function execLastId($query)
    {
        // Check to see if we have a connection
        if($this->pdo!=NULL)
        {
            // Execute the query and return the Id
            if($this->exec($query))
            {
                return $this->pdo->lastInsertId();
            }
        }

        return false;
    }

    /**
     * Prepare and execute against the database.
     *
     * @param string $query
     * @param array $params
     * @return array
     */
    public function prepare($query, $params)
    {
        // Check to see if we have a connection
        if($this->pdo!=NULL)
        {
            // Prepare the query
            $query = $this->pdo->prepare($query);

            // Execute the query
            $query->execute($params);

            return $query->fetchAll();
        }

        return false;
    }

    /**
     * Create a new PDO connection.
     *
     * @return PDO
     */
    protected function createConnection()
    {
        // See if we can attempt to make a connection
        if(isset($this->config[$this->type]))
        {
            $hasDSN = false;

            // Decide what DSN to use
            if($this->type=='mysql')
            {
                $hasDSN = true;

                $dsn = $this->getMySQLDSN();
            }

            // If a DSN has been found, make the connection
            if($hasDSN)
            {
                $username = $this->config[$this->type]['username'];
                $password = $this->config[$this->type]['password'];

                return new PDO($dsn, $username, $password); 
            }
        }

        return NULL;
    }

    /**
     * Get the MySQL DSN.
     *
     * @return string
     */
    protected function getMySQLDSN()
    {
        return 'mysql:host='.$this->config['mysql']['hostname'].';dbname='.$this->database;
    }

}

Database.php: is in the intermediary between the connection.

class Database {

    /**
     * Run a raw query on the database.
     *
     * @param string $query
     * @return array
     */
    public static function query($query)
    {
        // Create the connection
        $conn = new Connection;

        // Return the query
        return $conn->query($query);
    }

    /**
     * Execute a query on the database.
     *
     * @param string $query
     * @return int
     */
    public static function exec($query)
    {
        // Create the connection
        $conn = new Connection;

        // Return the query
        return $conn->exec($query);
    }

    /**
     * Execute a query and return the last inserted Id
     *
     * @param string $query
     * @return int
     */
    public static function execLastId($query)
    {
        // Create the connection
        $conn = new Connection;

        // Return the query
        return $conn->execLastId($query);
    }

    /**
     * Prepare and then execute a query.
     *
     * @param string $query
     * @param array $params
     * @return array
     */
    public static function prepare($query, array $params)
    {
        // Create the connection
        $conn = new Connection;

        // Return the query
        return $conn->prepare($query, $params);
    }
}

My question is: Is this efficient? Is there a simpler way? I'd appreciate any guidance. I like to think of myself as a beginner, but I really lack the experience in ensuring applications are efficient and reduce their own weight.

Answer 1

This is not efficient. Each time you execute a query you make a new Connection object, which in turn creates a new PDO connection. Connecting to the database will give you some overhead. You actually don't need to connect every time. You can just connect once and use that connection for subsequent queries.

So with a small change you can make your database class create the connection on first use:

class Database {

    /**
     * Reference to the connection
     */
    private static $connection = null;

    /**
     * Run a raw query on the database.
     *
     * @param string $query
     * @return array
     */
    public static function query($query)
    {
        // Get the connection
        $conn = self::getConnection();

        // Return the query
        return $conn->query($query);
    }

    public static function getConnection()
    {
      // Create the connection if needed.
      if (self::$connection === null)
      {
        self::$connection = new Connection;
      }
      // Return new or existing instance.
      return self::$connection;
    }

    ...