MySQL vs MySQLi vs PDO Performance Benchmark, Difference and Security Comparison - 2022

#pdo #mysql #php

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.