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]
1 2 3 4 5 |
$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]
1 2 3 4 5 |
$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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
$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]
1 2 3 4 5 |
$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
1 2 3 4 5 6 |
$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.