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.
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.
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
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 ().
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.
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.
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!
- How does someone die of a burn
- What is my personality? I am confused
- How can you color photos online?
- What was Michael Jackson's will
- Why does nobody reply to Quora
- Can a cop be a witness
- How does Fox News shape your opinion
- 5 tips for buying a home
- How a woman is shaped after pregnancy
- What are some of the least known Indian cuisines
- How do I solve my marital problems
- Are people with ASPD not normal? Why
- What is resilience
- Can an Indian adopt a foreign adult
- Why was Michael Jackson a legend
- Which is your favorite mythological incident
- What is dentist's favorite toothbrush
- Do you like being a man
- Why does vascular dementia worsen over time
- How authentic is Bitcoin
- How do we write nitric oxide III
- Just how simple are AP classes
- 1 is an integer
- Why did SEGA stop making consoles