MySQL vs MySQLi vs PDO Performance Benchmark, Difference and Security Comparison in 2016 and 2017 - Find Converter Wrapper and ORM Packages

Contents

1. PDO vs MySQLi vs MySQL

2. Difference to Connect Using MySQLi and PDO

3. PDO vs MySQLi vs MySQL Performance Benchmark

4. PDO vs MysQLi Security using Prepared Statements

5. Comparison between PDO and MySQLi Terms of Usage

6. Converting MySQL Applications to Use PDO or MySQLi

7. Package Recommendations for use with PDO and MySQLi

8. What to Use in a New Project: PDO or MySQLi?

1. PDO vs MySQLi vs MySQL

As we all know, MySQL is an Open Source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). MySQL is a central component of the LAMP Open Source Web application software stack (and other "AMP" stacks): Apache MySQL and PHP.

MySQL is an essential part of almost every Open Source PHP application. Good examples for PHP and MySQL based scripts are phpBB, osCommerce and Joomla.

PHP used to come with the original MySQL extension built-in which supports with older MySQL versions. However this extension was deprecated in favor of MySQLi (i for improved). At the same time PHP continued to evolve and the PDO (PHP Data Objects) extension was introduced to become a common interface for accessing many types of database.

MySQLi is another one of three ways to access a MySQL database server. Like the MySQL extension, the new MySQLi was designed to take better advantage of more recent MySQL server features.

The PHP Data Objects (PDO) extension defines a lightweight, common interface for accessing databases in PHP. Each database driver that is supported by PDO interface can expose database specific features, as well common functions.

PDO provides a data access abstraction layer, which means that, regardless of which database type you use, the same functions are available to perform queries and fetch results. PDO does not provide a full database abstraction. i.e. it does not rewrite SQL queries or emulate missing features. You should use a full-blown abstraction layer package if you need that capability.

2. Difference to Connect Using MySQLi and PDO

Either PDO and MySQLi offer an Object Oriented interface to the extension functions but MySQLi also offers a procedural API, which makes it easier for newcomers to understand. If you are familiar with the original PHP MySQL extension, you will find migration to the procedural MySQLi interface easier. Below is an example:

// PDO 
$pdo = new PDO( "mysql:" . "host=localhost;" . "dbname=database",
    'username', 'password');
 
// mysqli, procedural way 
$mysqli = mysqli_connect( 'localhost', 'username', 'password', 'database');
 
// mysqli, object oriented way 
$mysqli = new mysqli( 'localhost', 'username', 'password', 'database');

The main advantage of PDO over MySQLi is in the database support. PDO supports 12 different database types, in opposition to MySQLi, which supports MySQL only.

When you have to switch your project to use another database, PDO makes the process simpler. So all you have to do is change the connection string and at most a few queries if they use any syntax which is not supported by your new database.

3. PDO vs MysQLi Security using Prepared Statements

Both PDO and MySQLi provide support for prepared queries. This helps preventing SQL injection security issues, as long as you only use prepared queries to insert dynamic parameters in the queries.

For example, consider a hacker that tries to inject malicious SQL passing a forged value to the parameter code of a HTTP POST request that could be emulated like this:

$_POST['code'] = "'; DELETE FROM products; /*";

If you do not escape this value, it will be inserted in query as is, and so it would delete all rows from the products table.

One way to make queries more secure avoiding SQL injection is to quote string parameter values to insert escape characters.

// PDO, “manual” escaping

$name = PDO::quote( $_POST['code'] );
$pdo->query( "SELECT id, name FROM products WHERE code = $code" );

// mysqli, “manual” escaping

$name = mysqli_real_escape_string( $_POST['code'] );
$mysqli->query( "SELECT id, name FROM products WHERE name = '$code'" );

PDO::quote() not only escapes the string, but it also adds quotes. mysqli_real_escape_string() will only escape the string, so you will need to add the quotes manually.

// PDO, prepared statement

$pdo->prepare( 'SELECT id, name FROM products WHERE code = :code' );
$pdo->execute( array( ':code' => $_POST['code'] ) );

// mysqli, prepared statements

$query = $mysqli->prepare('SELECT id, name FROM users WHERE code = ?');
$query->bind_param('s', $_POST['code']);
$query->execute();

PDO also supports client side queries. This means that when it prepares a query, it does not have to communicate with the server.

Since MySQLi uses native prepared statements, it will may actually be faster to use mysqli_real_escape_string instead of using prepared statements, while it is still a secure solution.

4. PDO vs MySQLi vs MySQL Performance Benchmark

There were some PHP MySQL performance benchmark tests several years ago by Jonathan Robson as well by Radu Potop. Even though these tests were performed with PHP 5.3 and nowadays we are using PHP 7 or later, let's consider these results as reference.

Basically they show that for SELECT queries using prepared statements MySQLi runs a bit faster. Still it may not be significant depending on your purposes.

Keep in mind that PDO by default uses client side prepared statements emulation. When using native prepared statements, there is an additional round trip to the server to prepare the statement, so the overall query execution time may be actually greater than when using native prepared statements for running a query only once.

As mentioned above, you can use mysqli_real_escape_string function to quote dynamic parameters like you would do when using the original MySQL extension. This is like emulating prepared queries the way it is done with PDO because you would not need to perform an additional round trip to the server to prepare the statement.

5. Comparison between PDO and MySQLi Terms of Usage

While MySQLi has its advantages accessing MySQL server features, PDO sometimes has a leg up and may be a better fit for the user, depending on personal preferences and convenience. Like most things, the option that works best depends on whom you ask and what situation you need MySQLi or PDO for.

MySQLi only works with MySQL databases, whereas PDO is flexible and able to work with multiple database systems, including IBM, Oracle and MySQL. If you ever have to switch databases or provide a database independent solution, using MySQLi directly is not the best option. You may still use MySQLi in database independent solutions using an abstraction layer.

Binding parameters with MySQLi is not as easy or flexible as with PDO. MySQLi uses a numbered parameter system and does no’t support named parameters like PDO. MySQLi has good support and maintenance, making the transition to and use the new system safe and secure.

You will be able to utilize new features available in the newer versions of MySQL servers. This is one of the biggest advantages of MySQLi. PDO may not have extensive support to take full advantage of MySQL’ newer capabilities.

The installation process with MySQLi is not only easy, but is automatic when the PHP 5 MySQL extension that comes built-in Windows or Linux distributions.

6. Converting MySQL Applications to Use PDO or MySQLi

If you have a project using the old MySQL extension and you need to convert it to use MySQLi or PDO, it may take you a while to rewrite it if you were not using a database abstraction layer.

If you need a quick a easy solution that does not require to change much your code, you can try either the package PDO or the MySQLi to use each of these extensions by the means of MySQL wrapper functions that call mysql_* functions using PDO by Aziz S. Hussain or PHP MySQL to MySQLi by Dave Smith for PDO and MySQLi extension functions respectively.

This will allow you to make your code run on PHP 7 while you do a real migration of code that does not require a wrapper.

Dave Smith also has written a tutorial on how to migrate mysql code to mysqli considering the differences between those extensions.

7. Package Recommendations for use with PDO and MySQLi

There are many packages that you can use for varied purposes to access MySQL using either MySQLi or PDO.

Below are a some of the more recent PHP packages that provide secure and efficient wrappers for PDO several purposes.

PDO Backup and Synchronize Database

PHP Multi MySQLDump (Alessandro Quintiliani)

PHP Backup to WebDav Server (Dmitry Mamontov)

PHP Database Synchronize Tables (Ettore Moretti)

Synchronize tables between two database using PDO

DB Backup Class (Raul)

Generate a MySQL database backup using PDO

PHP Database Sync (Jacob Fogg)

Synchronize tables of different databases with PDO

MySQLi Backup Database

Danen MySQL Backup (Gerry Danen)

Create database backup SQL statements using MySQLi

Ah MySQLi (Atabak Hosein Nia)

Execute common MySQL queries using MySQLi

DBMysqli (Osama Salama)

Access MySQL databases with mysql and mysqli

PDO Security and Authentication

PHP Secure Token Generator (Aleksandar Zivanovic)

Create and validate tokens stored in MySQL table

PHP OTP Login (Bijaya Kumar Behera)

Authenticate users using the computer MAC address

MySQLi Security and Authentication

PHP OAuth API Library with Tutorial Example Client for Any OAuth1 and OAuth2 Server (Manuel Lemos)

PDO User Registration and Login

Secure PHP Login System (Subin Siby)

Register and login users using a database via PDO

PHP Auth Class (Con vertor)

Register and login users stored in a database

PHP Secure Login and Registration (Ashraf Gheith)

Register and login users in a database with PDO

PDO Database Access and Abstraction

Multiple PHP PDOStatement Iterator (Matthew Daniel)

Iterate over multiple query results using PDO

PHP PDO OCI (Eustaquio Rangel de Oliveira)

Access Oracle databases using a PDO class wrapper

DB Abstract Model (Guillermo Murillo)

Execute MySQL queries calling stored procedures

MySQLi Database Access and Abstraction

Metabase (Manuel Lemos)

PHP Database abstraction layer RDBMS independent

PHP CRUD REST API Server (Bharat Parmar)

REST API server with MySQLi based CRUD operations

Extended MySQLi ()

MySQL database access wrapper using MySQLi

PDO ORM (Object-Relational Mapping)

Tiny PHP ORM Framework (Victor Bolshov)

Map objects to databases using composed queries

Caribu ORM (Maik Greubel)

Map objects to databases records using annotations

Potato ORM (Adeniyi Ibraheem)

Store and retrieve objects in databases using ORM

torm (Eustaquio Rangel)

Store and retrieve objects using ActiveRecord

SlimDb (Marcelo Entraigas)

Access different database types using PDO

MySQLi ORM (Object-Relational Mapping)

DB Accelerator (Yazan Tommalieh)

Map MySQL table records to objects using MySQLi

8. What to Use in a New Project: PDO or MySQLi?

While PDO may be slightly slower and not take full advantage of MySQL server features, it may be simpler to write less database dependent code while having more readable code using named parameters for prepared queries. So either option may be good depending on what you value.

This article tried to give some criterion you should consider when deciding which one is the best for you.

If you liked this article recommend it to other developers using the like and share buttons on this page. If you have questions or comments, post a comment below.