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.
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
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.
PDO or MySqli: The pros and cons
By Game Changer →
Tuesday, December 8, 2015
