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.

swapping pieces to make it go faster

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

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

// 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.

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

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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *