Is PDO faster than MySQLi

Understand PHP, PDO and Prepared Statements step by step

What are PDOs?

PDO stands for PHP Data Objects, introduced with PHP 5.1. In summary, PDO is an abstract database interface that can access different databases using different drivers (MySQL, PostgreSQL, Oracle, ...).

What are prepared statements?

Prepared statements are precompilable query templates that can be executed repeatedly with various parameters to save resources. Compared to ‘normal’, completely executed queries over and over again, prepared statements offer a performance advantage.

PDO advantages

Is PDO slower than mysqli?

It is often said that PDO is slower than mysqli. Benchmarks with prepared statements show that the speed advantage of mysqli is a maximum of 6%. In other benchmarks, on the other hand, PDO are completely ahead. And with non-prepared statements benchmarks, PDOs reach the same speed as normal MySQL queries, mysqli far behind.

Clearer

Even if mysqli should be slightly faster, the PDO syntax is much clearer. A question mark (mysqli) simply loses out in comparison to freely selectable parameter names (PDO). In addition, you can assign the named placeholders individually, in contrast to mysqli. _bindparam (“sss”, $ string, $ string2, $ string3) comes to mind 🙂

Prepared statements with PDO: structure

connection

Anyone who has already dealt with PHP and mysqli does not have to learn anything new for PDOs.

The first thing to do is to establish a connection. This is done by

Connection with exception

You can also write the whole thing in a try-catch block to catch the PDO's own exceptions:

Prepare query: prepare ()

Like mysqli, the PDO interface provides a prepare () -Method available for preparing and optimizing queries from the database. In contrast to mysqli, there is no “?” In the WHERE clause here. but named parameters, which are identified by a preceding colon (here :Surname).

Named parameters with values ​​'bind'

Just like mysqli, PHP Data Objects has a parameter binding method, bindParam (). This can be used to bind the query parameters to PHP variables so that the parameters then receive the variable values ​​when the query is executed.

Now is the variable $ name to the parameter :Surname bound, and :Surname is replaced in the query with the one that $ name contains at execution time. In the example code this would now be “Foobar”.

Named parameter as an associative array

Alternatively, the parameters can also be passed as an array to the execute () method at the end:

Execute PDO query: execute ()

After we have prepared a query and bound the parameters, we can execute it:

Get result records: fetch (), fetchObject () and fetchAll ()

To deliver the results, PDO gives you the methods fetch (), fetchObject () and fetchAll ().

Fetch modes

You can modify fetch () and fetchAll () using optional parameters:

  • PDO :: FETCH_BOTH : gets an associative and a numeric array (is default)
  • PDO :: FETCH_ASSOC : only fetches an associative array, indices are column names
  • PDO :: FETCH_OBJ : fetches an object, properties are the same as column names
  • PDO :: FETCH_INTO : pulls the result into an object instance of the class MyClass

Set fetch mode in advance: setFetchMode ()

The fetch mode can also be set a little cleaner with the setFetchMode () method:

Close PDO connection

The PDO connection can easily be closed by equating it with ZERO.

PDO conclusion

Prepared statements are essential with PHP & PDO clearer, more powerful and more flexible than with mysqli. The various benchmark results, in which only one spoke in favor of mysqli, should not deter PDO. Rather, the features already mentioned speak so much in favor of PDO that you can safely forget about mysqli. Even if in some cases it should be a few percentage points faster than PDO.

Danger:

However, you shouldn't always use prepared statements now. A ‘normal’ $ pdo-> query (“MySQL Query) is about three times as fast as a prepared statement with PDO. Prepared statements should only be used if the same query is executed over and over again with different parameters.

Is something still unclear? Are you missing something in this article? Help me improve it, and please leave a comment or rate it below!

If you liked this article, then simply subscribe to the free newsletter and receive all articles conveniently via RSS or email!