On connecting to MySQL from PHP

The various PHP to MySQL connectors

There are a couple of ways to connect to MySQL from PHP. There is the ole traditional mysql_* api. We don’t use this any more. The recommended APIs are mysqli and pdo_mysql. They provide prepared statement support with parameter binding — which helps a lot with code quality and site security.

Establish the connection. Quickly.

Check your connection type using mysql_thread_id()/mysql_get_host_info(). With persistent connections, the thread-id will loop around instead of continuously growing. The host info will confirm whether you are connecting over TCP/IP or a Unix Socket.

These tests were done using the traditional MySQL api, and the newer PDO and MySQLi interfaces on the following stack:

MySQL 5.5
PHP 5.3 (php-fpm/fcgi) with mysql_nd driver
Apache 2.2
Ubuntu 12 on an i7 server

Note: All timings are intended for relative comparison. Performed over localhost.

Socket Type	API	Is Persistent?	Time
Unix Socket	PDO	YES	0.25 msec
TCP/IP	PDO	YES	0.30 msec
TCP/IP	mysqli	YES	0.30 msec
Unix Socket	mysqli	YES	0.30 msec
Unix Socket	mysql	YES	0.40 msec
TCP/IP	mysql	YES	0.45 msec
Unix Socket	mysqli	no	0.50 msec
Unix Socket	mysql	no	0.65 msec
Unix Socket	PDO	no	0.70 msec
TCP/IP	mysqli	no	0.80 msec
TCP/IP	PDO	no	0.90 msec
TCP/IP	mysql	no	0.90 msec

Persistent connections over unix sockets are fastest to create. Non-persistent connections over TCP/IP are slowest to create.

Fetch data. Avoid SQL-Injection.

API	Is Secure?	Time
mysql_query	no	0.40 msec
pdo->query	no	0.45 msec
pdo->prepare	YES	0.45 msec
mysqli_query	no	0.50 msec
mysqli_prepare	YES	0.85 msec

Nothing wrong with swapping slow parts out for new speedy ones.

// 0.40 msec and 1776 bytes
// original (deprecated) mysql api [very vulnerable to sql-injection]

$query = mysql_query(

"select * from bugs b join apps a using(AppId) "

."join platforms p using (PlatformId) where BugId = 2"

);

$result = mysql_fetch_object($query);

// 0.50 msec and 1776 bytes
// mysqli with lame string-building [very vulnerable to sql-injection]

$query = $mysqli->query(

"select * from bugs b join apps a using(AppId) "

."join platforms p using (PlatformId) where BugId = 2"

);

$result = $query->fetch_object();

// 0.85 msec and 3235 bytes
// mysqli with secure bindings
// this is a custom object builder that i’ve been using for a long time.
// I even ported it to PHP 5.3; PDO does this 2x faster while using less bandwidth.

$prepare = $mysqli->prepare(

"select * from bugs b join apps a using(AppId) "

."join platforms p using (PlatformId) where BugId = ?"

);

$value = 2;

$prepare->bind_param("i", $value);

$prepare->execute();

$metadata = $prepare->result_metadata();

$fields = $metadata->fetch_fields();

$fieldNames = array();

$resultObj = new stdClass;

$i = 0;

foreach( $fields as $field )

$fieldNames[$i++] = &$resultObj->{$field->name};

call_user_func_array(array( &$prepare, "bind_result"), refHelper($fieldNames));

$prepare->fetch();

// a helper for newer php to allow passing by reference

function refHelper($args)

{

// explicit references for php 5.3+

$refs = array();

foreach( $args as $key => $value )

$refs[$key] = &$args[$key];

return $refs;

}

// 0.45 msec and 1776 bytes
// PDO with lame string-building [very vulnerable to sql-injection]

$stmt = $pdo->query(

"select * from bugs b join apps a using(AppId) "

."join platforms p using (PlatformId) where BugId = 2"

);

$result = $stmt->fetch(PDO::FETCH_OBJ);

// 0.45 msec and 1776 bytes
// PDO with secure binding

$stmt = $pdo->prepare(

"select * from bugs b join apps a using(AppId) "

."join platforms p using (PlatformId) where BugId = :BugId"

);

$stmt->execute(array(":BugId" => 2));

$result = $stmt->fetch(PDO::FETCH_OBJ);

Winner: PDO with secure binding. It is the cleanest and safest path without sacrificing performance.

There is no significant performance reason to use the old mysql api and a big security reason to use PDO.

This post is brought to you from the “doing everything but working on the actual product” file.

Posted on 19 June 2012

On connecting to MySQL from PHP

The various PHP to MySQL connectors

Establish the connection. Quickly.

Fetch data. Avoid SQL-Injection.

Nothing wrong with swapping slow parts out for new speedy ones.

Leave a Reply Cancel reply