PDO or MySqli: The pros and cons

Choosing a database wrapper in you development project.

Moving an application from one database to another isn't very common, but sooner or later you may find yourself working on another project using a different RDBMS. If you're at home with PDO then there will at least be one thing less to learn at that point.

Apart from that I find the PDO API a little more intuitive, and it feels more truly object oriented. mysqli feels like it is just a procedural API that has been objectified, if you know what I mean. In short, I find PDO easier to work with, but that is of course subjective.
PDO is the standard, it's what most developers will expect to use. mysqli was essentially a bespoke solution to a particular problem, but it has all the problems of the other DBMS-specific libraries. PDO is where all the hard work and clever thinking will go.

Performance Results

Here are the results of the tests. Lower values are better, of course.

PDO results for 0.1M SQL queries

Query Time in seconds
insert 15.23874929331
select 18.29382394421
update 14.93284923728
delete 17.92102038303
MySQLi results for 100k queries

Query Time in seconds
insert 21.98739839490
select 26.01564064026
update 27.35169916153
delete 21.15891308745

PDO results for 1M queries
Query Time in seconds
insert 139.2347803431
select 207.1249543506
update 151.2345734537
delete 141.3245435434
MySQLi results for 1M queries

Query Time in seconds
insert 204.2342353454
select 291.4354655432
update 221.0213423434
delete 231.1456456456

Summary

	PDO	MySQLi
Database support	12 different drivers	MySQL only
API	OOP	OOP + procedural
Connection	Easy	Easy
Named parameters	Yes	No
Object mapping	Yes	Yes
Prepared statements (client side)	Yes	No
Performance	Fast	Fast
Stored procedures	Yes	Yes

Named Parameters

This is another important feature that PDO has; binding parameters is considerably easier than using the numeric binding:

$params = array(':username' => 'test', ':email' => $mail, ':last_login' => time() - 3600);
     
$pdo->prepare('
    SELECT * FROM users
    WHERE username = :username
    AND email = :email
    AND last_login > :last_login');
     
$pdo->execute($params);

...opposed to the MySQLi way:

$query = $mysqli->prepare('
    SELECT * FROM users
    WHERE username = ?
    AND email = ?
    AND last_login > ?');
     
$query->bind_param('sss', 'test', $mail, time() - 3600);
$query->execute();

The question mark parameter binding might seem shorter, but it isn't nearly as flexible as named parameters, due to the fact that the developer must always keep track of the parameter order; it feels "hacky" in some circumstances.

Unfortunately, MySQLi doesn't support named parameters.

MySQLi vs PHP Object Mapping

Both PDO and MySQLi can map results to objects. This comes in handy if you don't want to use a custom database abstraction layer, but still want ORM-like behavior. Let's imagine that we have a User class with some properties, which match field names from a database.

class User {
    public $id;
    public $first_name;
    public $last_name;
     
    public function info()
    {
        return '#'.$this->id.': '.$this->first_name.' '.$this->last_name;
    }
}

API Case:

Both offer an object-oriented API, but MySQLi also offers a procedural API - which makes it easier for newcomers to understand. If you are familiar with the native PHP MySQL driver, you will find migration to the procedural MySQLi interface much easier. On the other hand, once you master PDO, you can use it with any database you desire!

Summery:

After the whole reviewing, PDO wins this battle with ease. With support for twelve different database drivers (eighteen different databases!) and named parameters. We can ignore the small performance loss, and get used to its API. Also from a security point.

Post Tags:

best coding choice database mysqli pdo

Sael

I'm Sael. An expert coder and system admin. I enjoy to make codes easy for novice.

Website: fb/Fujael