PDO or Mysqli?

classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|

PDO or Mysqli?

Ross Little
Hi Guys

I'm relatively new to ZF, but it seems that just about every tutorial out there uses the Mysqli adapter but everywhere else, I read about how much more secure and efficient the PDO adapter is.  Is this true?

In order to take advantage of PDO, do I only need to change my resources.db.adapter in my application.ini file?  I tried this and I didn't notice any difference. I mean, i don't imagine it'll make a discernable difference on my development version of the code, but it didn't break anything.  

Any thoughts?

Ross
Reply | Threaded
Open this post in threaded view
|

Re: PDO or Mysqli?

Ross Little
bump...
Reply | Threaded
Open this post in threaded view
|

Re: PDO or Mysqli?

umpirsky
I use mysqli, because I think it's faster, but I'm not really sure.

Regards,
Saša Stamenković


On Wed, Feb 17, 2010 at 6:37 PM, Ross Little <[hidden email]> wrote:

bump...
--
View this message in context: http://n4.nabble.com/PDO-or-Mysqli-tp1556941p1559054.html
Sent from the Zend Framework mailing list archive at Nabble.com.

Reply | Threaded
Open this post in threaded view
|

Re: PDO or Mysqli?

monk.e.boy
This post has NOT been accepted by the mailing list yet.
I use PDO because that's what doctrine makes me use.
Reply | Threaded
Open this post in threaded view
|

Re: PDO or Mysqli?

David Muir
This post has NOT been accepted by the mailing list yet.
PDO here as well (although I've used mysqli on servers that didn't have PDO)
There are some differences with binding parameters between the two. I personally like PDO's style better.
PDO also supports named parameters, which lets you do some things that you can't with mysqli.

I'd suggest looking at the two manual entries and comparing the two (especially the sample code) to get an idea of the differences.

http://www.php.net/manual/en/book.mysqli.php
http://www.php.net/manual/en/book.pdo.php

That said, Zend_Db adds another abstraction layer so most of the time you won't even notice much of a difference.

Cheers,
David
Reply | Threaded
Open this post in threaded view
|

Re: PDO or Mysqli?

Ralph Schindler-2
In reply to this post by Ross Little
In terms of the general use cases (anything documented in the manual,
Zend_Db_Table, Zend_Db_Select) - you will see no discernible difference.
The only time you'll see any differences is if you are using extension
specific features.

I suggest you have a look at the chart at the bottom of this page:

http://us3.php.net/manual/en/mysqli.overview.php

This will give you a better idea for what goes on under the hood in
Zend_Db_Adapter_Mysqli and Zend_Db_Adapter_Pdo_Mysql.

Beyond that, both extensions are as available on all platforms I've
seen, so either choice is good, and generally, switching in mid-stream
is a piece of cake.

-ralph

Ross Little wrote:

> Hi Guys
>
> I'm relatively new to ZF, but it seems that just about every tutorial out
> there uses the Mysqli adapter but everywhere else, I read about how much
> more secure and efficient the PDO adapter is.  Is this true?
>
> In order to take advantage of PDO, do I only need to change my
> resources.db.adapter in my application.ini file?  I tried this and I didn't
> notice any difference. I mean, i don't imagine it'll make a discernable
> difference on my development version of the code, but it didn't break
> anything.  
>
> Any thoughts?
>
> Ross
Reply | Threaded
Open this post in threaded view
|

Re: PDO or Mysqli?

umpirsky
So, which one is better in terms of performances?

Regards,
Saša Stamenković


On Thu, Feb 18, 2010 at 7:37 PM, Ralph Schindler <[hidden email]> wrote:
In terms of the general use cases (anything documented in the manual, Zend_Db_Table, Zend_Db_Select) - you will see no discernible difference. The only time you'll see any differences is if you are using extension specific features.

I suggest you have a look at the chart at the bottom of this page:

http://us3.php.net/manual/en/mysqli.overview.php

This will give you a better idea for what goes on under the hood in Zend_Db_Adapter_Mysqli and Zend_Db_Adapter_Pdo_Mysql.

Beyond that, both extensions are as available on all platforms I've seen, so either choice is good, and generally, switching in mid-stream is a piece of cake.

-ralph


Ross Little wrote:
Hi Guys

I'm relatively new to ZF, but it seems that just about every tutorial out
there uses the Mysqli adapter but everywhere else, I read about how much
more secure and efficient the PDO adapter is.  Is this true?

In order to take advantage of PDO, do I only need to change my
resources.db.adapter in my application.ini file?  I tried this and I didn't
notice any difference. I mean, i don't imagine it'll make a discernable
difference on my development version of the code, but it didn't break
anything.  
Any thoughts?

Ross

Reply | Threaded
Open this post in threaded view
|

Re: PDO or Mysqli?

Nicolas GREVET
I guess this was the interesting part:

> In terms of the general use cases [...] you will see no discernible difference.

Regards,
- Nicolas


Саша Стаменковић wrote:

> So, which one is better in terms of performances?
>
> Regards,
> Saša Stamenković
>
>
> On Thu, Feb 18, 2010 at 7:37 PM, Ralph Schindler
> <[hidden email] <mailto:[hidden email]>> wrote:
>
>     In terms of the general use cases (anything documented in the
>     manual, Zend_Db_Table, Zend_Db_Select) - you will see no discernible
>     difference. The only time you'll see any differences is if you are
>     using extension specific features.
>
>     I suggest you have a look at the chart at the bottom of this page:
>
>     http://us3.php.net/manual/en/mysqli.overview.php
>
>     This will give you a better idea for what goes on under the hood in
>     Zend_Db_Adapter_Mysqli and Zend_Db_Adapter_Pdo_Mysql.
>
>     Beyond that, both extensions are as available on all platforms I've
>     seen, so either choice is good, and generally, switching in
>     mid-stream is a piece of cake.
>
>     -ralph
>
>
>     Ross Little wrote:
>
>         Hi Guys
>
>         I'm relatively new to ZF, but it seems that just about every
>         tutorial out
>         there uses the Mysqli adapter but everywhere else, I read about
>         how much
>         more secure and efficient the PDO adapter is.  Is this true?
>
>         In order to take advantage of PDO, do I only need to change my
>         resources.db.adapter in my application.ini file?  I tried this
>         and I didn't
>         notice any difference. I mean, i don't imagine it'll make a
>         discernable
>         difference on my development version of the code, but it didn't
>         break
>         anything.  
>         Any thoughts?
>
>         Ross
>
>
Reply | Threaded
Open this post in threaded view
|

Re: PDO or Mysqli?

David Muir
In reply to this post by monk.e.boy
PDO here as well (although I've used mysqli on servers that didn't have PDO)
There are some differences with binding parameters between the two. I personally like PDO's style better.
PDO also supports named parameters, which lets you do some things that you can't with mysqli.

I'd suggest looking at the two manual entries and comparing the two (especially the sample code) to get an idea of the differences.

http://www.php.net/manual/en/book.mysqli.php
http://www.php.net/manual/en/book.pdo.php

That said, Zend_Db adds another abstraction layer so most of the time you won't even notice much of a difference.

Cheers,
David
Reply | Threaded
Open this post in threaded view
|

Re: PDO or Mysqli?

umpirsky
Does anyone knows if there are differences in performances?

Regards,
Saša Stamenković


On Wed, Feb 24, 2010 at 1:17 PM, David Muir <[hidden email]> wrote:

PDO here as well (although I've used mysqli on servers that didn't have PDO)
There are some differences with binding parameters between the two. I
personally like PDO's style better.
PDO also supports named parameters, which lets you do some things that you
can't with mysqli.

I'd suggest looking at the two manual entries and comparing the two
(especially the sample code) to get an idea of the differences.

http://www.php.net/manual/en/book.mysqli.php
http://www.php.net/manual/en/book.pdo.php

That said, Zend_Db adds another abstraction layer so most of the time you
won't even notice much of a difference.

Cheers,
David
--
View this message in context: http://n4.nabble.com/PDO-or-Mysqli-tp1556941p1567381.html
Sent from the Zend Framework mailing list archive at Nabble.com.

Reply | Threaded
Open this post in threaded view
|

Re: PDO or Mysqli?

Hector Virgen
Not the most scientific analysis but this guy seems to get better performance out of PDO:


--
Hector


On Wed, Feb 24, 2010 at 6:18 AM, Саша Стаменковић <[hidden email]> wrote:
Does anyone knows if there are differences in performances?

Regards,
Saša Stamenković



On Wed, Feb 24, 2010 at 1:17 PM, David Muir <[hidden email]> wrote:

PDO here as well (although I've used mysqli on servers that didn't have PDO)
There are some differences with binding parameters between the two. I
personally like PDO's style better.
PDO also supports named parameters, which lets you do some things that you
can't with mysqli.

I'd suggest looking at the two manual entries and comparing the two
(especially the sample code) to get an idea of the differences.

http://www.php.net/manual/en/book.mysqli.php
http://www.php.net/manual/en/book.pdo.php

That said, Zend_Db adds another abstraction layer so most of the time you
won't even notice much of a difference.

Cheers,
David
--
View this message in context: http://n4.nabble.com/PDO-or-Mysqli-tp1556941p1567381.html
Sent from the Zend Framework mailing list archive at Nabble.com.


--
Hector Virgen
Reply | Threaded
Open this post in threaded view
|

Re: PDO or Mysqli?

Ulf Wendel
That blog posting is puzzling me, if not to say, I believe it is misleading.

Hector Virgen schrieb:
> Not the most scientific analysis but this guy seems to get better
> performance out of PDO:

Any of the PHP APIs are just tiny wrappers on top of the underlying C
libraries. Mapping from the C call to a PHP call is always about about
equally fast no matter how you do the mapping - in ext/mysql, ext/mysqli
or PDO_MYSQL. If you compare equivalent PHP API calls with each other
they should be very, very close together in performance.

I assume the blog posting is comparing apples and oranges, likely
without the blog author being aware of it. For example, PDO is using a
prepared statement emulation by default for MySQL. It could well be that
the blog posting compares a native prepared statement and a non-prepared
statement. Or, the blog posting has been written in 2009, it could be
that for PDO persistent connections had been used whereas for ext/mysqli
non-persistent connections had been used.

Moral is, forget about API performance. There's not much to squeeze out
of how you map the very same underlying C library calls into PHP API
calls. But it can help a lot to use proper API calls for the task.

Ulf

--
Ulf Wendel, MySQL
Sun Microsystems GmbH,   Sonnenallee 1,   D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering     Muenchen: HRB161028
Reply | Threaded
Open this post in threaded view
|

Re: PDO or Mysqli?

BillKarwin

On Feb 24, 2010, at 9:23 AM, Ulf Wendel wrote:

> Moral is, forget about API performance. There's not much to squeeze  
> out of how you map the very same underlying C library calls into PHP  
> API calls. But it can help a lot to use proper API calls for the task.


For those who don't know, Ulf was the lead for the mysqlnd driver.  So  
I'd say he knows about this issue. :-)

I agree (for what it's worth).  In 2008, I gave the following answer  
for a similar question about the performance of ext/mysql versus ext/
mysqli.  I believe the same applies when comparing ext/pdo_mysql.

> The MySQL extension is very slightly faster than MySQLi in most  
> benchmarks I've seen reported. The difference is so slight, however,  
> that this should probably not be your criterion for deciding between  
> the two.
>
> Other factors dwarf the difference in performance between mysql and  
> mysqli. Using mod_php or FastCGI, a bytecode cache like APC, or  
> using data caching judiciously to reduce database hits, are far more  
> beneficial for overall performance of PHP scripts than the choice of  
> MySQL extension.
>
> Don't be penny wise and pound foolish! :-)
>
http://stackoverflow.com/questions/171400/which-is-fastest-in-php-mysql-or-mysqli/171445#171445



Regards,

Bill Karwin
Reply | Threaded
Open this post in threaded view
|

Re: PDO or Mysqli?

Vincent de Lau
In reply to this post by Ulf Wendel


> -----Oorspronkelijk bericht-----
> Van: [hidden email] [mailto:[hidden email]]
> Verzonden: woensdag 24 februari 2010 18:23
>
> That blog posting is puzzling me, if not to say, I believe it is
misleading.

>
> Hector Virgen schrieb:
> > Not the most scientific analysis but this guy seems to get better
> > performance out of PDO:
>
> Any of the PHP APIs are just tiny wrappers on top of the underlying C
> libraries. Mapping from the C call to a PHP call is always about about
> equally fast no matter how you do the mapping - in ext/mysql, ext/mysqli
> or PDO_MYSQL. If you compare equivalent PHP API calls with each other
> they should be very, very close together in performance.
>
> I assume the blog posting is comparing apples and oranges, likely
> without the blog author being aware of it. For example, PDO is using a
> prepared statement emulation by default for MySQL. It could well be that
> the blog posting compares a native prepared statement and a non-prepared
> statement. Or, the blog posting has been written in 2009, it could be
> that for PDO persistent connections had been used whereas for ext/mysqli
> non-persistent connections had been used.
>
> Moral is, forget about API performance. There's not much to squeeze out
> of how you map the very same underlying C library calls into PHP API
> calls. But it can help a lot to use proper API calls for the task.

One thing I think you should keep in mind with this, is that there is a
layer in between which is Zend_Db. Although the bare PHP speed might be
equal, it might well be that the PDO_MYSQL aligns better with the Zend_Db
architecture then ext/mysqli.

I would welcome a proper benchmark to test this. If the difference is as big
as in the article, it might be a good idea to repeat the test with a
profiler attached to see what causes the problem.

Vincent de Lau
 [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: PDO or Mysqli?

Ulf Wendel
Vincent de Lau schrieb:

>
>> -----Oorspronkelijk bericht-----
>> Van: [hidden email] [mailto:[hidden email]]
>> Verzonden: woensdag 24 februari 2010 18:23
>>
>> That blog posting is puzzling me, if not to say, I believe it is
> misleading.
>> Hector Virgen schrieb:
>>> Not the most scientific analysis but this guy seems to get better
>>> performance out of PDO:
>> Any of the PHP APIs are just tiny wrappers on top of the underlying C
>> libraries. Mapping from the C call to a PHP call is always about about
>> equally fast no matter how you do the mapping - in ext/mysql, ext/mysqli
>> or PDO_MYSQL. If you compare equivalent PHP API calls with each other
>> they should be very, very close together in performance.
>>
>> I assume the blog posting is comparing apples and oranges, likely
>> without the blog author being aware of it. For example, PDO is using a
>> prepared statement emulation by default for MySQL. It could well be that
>> the blog posting compares a native prepared statement and a non-prepared
>> statement. Or, the blog posting has been written in 2009, it could be
>> that for PDO persistent connections had been used whereas for ext/mysqli
>> non-persistent connections had been used.
>>
>> Moral is, forget about API performance. There's not much to squeeze out
>> of how you map the very same underlying C library calls into PHP API
>> calls. But it can help a lot to use proper API calls for the task.
>
> One thing I think you should keep in mind with this, is that there is a
> layer in between which is Zend_Db. Although the bare PHP speed might be
> equal, it might well be that the PDO_MYSQL aligns better with the Zend_Db
> architecture then ext/mysqli.

Vincent,

that is a good and important point.

Look, I'm no longer a PHP application developer myself. Back in the past
I loved all those abstraction layers so much. Nowadays, I am the test
slave of Andrey Hristov, the only person in the world that knows the C
implementation of mysqlnd inside out. Therefore, I'm tend to look at
things from an implementors and not from a users perspective.

Let's you issue a query "SELECT a FROM test WHERE b = 1 LIMIT :myvalue"
with PDO_MYSQL. By default PDO will use its prepared statement emulation
to map this query into a non-prepared statement. It will do placeholder
substitution and map your PHP call into something like this (pseudo
C-API calls):

   mysql_query("SELECT a FROM test WHERE b = 1 LIMIT 2")

No more. OK, I'm omitting some string escape calls because they are
irrelevant for our discussion. On the line you get:

  PHP sends query to MySQL  -->  MySQL runs query
  PHP buffers all results   <--  MySQL sends result

Now, let's say you do the same with ext/mysqli. First, you need to use
"SELECT a FROM test WHERE b 1 LIMIT ?" because MySQLs prepared statement
syntax is not aware of named parameters. Second, you'll have to do a lot
more steps. Let's look at the pseudo C-API calls again:

  stmt = mysql_stmt_init()
  mysql_stmt_prepare("SELECT a FROM test WHERE b = 1 LIMIT ?")
  mysql_stmt_bind_param(1, 2)
  mysql_stmt_execute()

Who will this look on the line?

   PHP acquires a statement handler

   PHP asks MySQL to prepare the statement --> MySQL checks the SQL
   PHP receives OK                         <-- MySQL tells PHP SQL is OK

   PHP binds variables and sends variables --> MySQL runs query
   PHP retrieves results                   <-- MySQL sends results

As you can see, you already got two round trips from the client to the
server versus one above. But there are more differences: buffered versus
unbuffered results. Native prepared statement results are unbuffered by
default. Depending on your code that can make a significant difference.

Now, can you tell that ext/mysqli is less good, less adapted for
Zend_Db? There ain't no clear answer to this. If you do not really need
prepared statements, why do you use it: what is there in "SELECT a FROM
test WHERE b = 1 LIMIT 2" that forces you to use prepared statements?
 From a server perspective prepared statements are more complex to
handle. You need internal prepare structures, you need to keep record of
the handles, you get the hassle with people not freeing handles timely
and so on.

Note that I am not saying you must not use prepared statements. Nor am I
saying Zend_Db is broken by design or something.

So, in the above example PDO ended up with a stellar result: 50% less
round trips, if not more due to buffered vs. unbuffered result sets.
Unfortunately, you can find many bug reports on PDO and LIMIT. How come?

The PDO emulation does not know that MySQL wants you to provide a number
after LIMIT. PDO may go for a safe default and do "SELECT a FROM test
WHERE b 1 LIMIT :myvalue" -> "SELECT a FROM test WHERE b 1 LIMIT '2'" -
eeek, no string allowed here. Of course, the workaround is
straightforward and easy: you tell PDO its a number not a string.

Continue searching bug reports and you will learn that there are more
such issues. Am I saying PDO is broken, well, yes, I don't like the
emulation. I hate it. In particular I hate the support for stone-age
MySQL versions in PDO_MYSQL --- calm don't that's my private view, it is
  nothing you, as a PHP application developer need to take care of. One
last comment on this: Brian and Lukas have written excellent blog
postings why we neither want PS emulations like PDO nor native prepared
statements in todays web applications. Thing is, no database vendor I am
aware of has a good offering for you.

What is the moral of the story? There is no simple black and white, good
and bad, less or better adopted to Zend_Db. It is really important to
know what happens behing the scene to pick the correct tool and API call.

Yes, I believe the blog author that he has observed the figures he had
posted. But, yes, I believe also that different, cleverer PHP coding may
align figures in a way that performance differences between ext/mysql,
ext/mysqli and PDO_MYSQL become irrelevant.

When benchmarking, keep an eye on:

  persistent vs. non-persistent connections
  (persistent with and without change_user)

  buffered vs. unbuffered result sets

  native prepared statement vs. non prepared statements

Ulf

--
Ulf Wendel, MySQL
Sun Microsystems GmbH,   Sonnenallee 1,   D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering     Muenchen: HRB161028
Reply | Threaded
Open this post in threaded view
|

Re: PDO or Mysqli?

BillKarwin
On Feb 24, 2010, at 11:57 AM, Ulf Wendel wrote:
> As you can see, you already got two round trips from the client to  
> the server versus one above.

Are you sure the round-trips are a strong determiner for query  
performance?

MySQLPerformanceBlog.com did some benchmarks in an article about  
"Prepared Statements" (http://www.mysqlperformanceblog.com/2006/08/02/mysql-prepared-statements/ 
).

Peter Zaitsev wrote:

> I’ve done a simple benchmark (using SysBench) to see performance of  
> simple query (single row point select) using standard statement,  
> prepared statement and have it served from query cache. Prepared  
> statements give 2290 queries/sec which is significantly better than  
> 2000 with standard statements but it is still well below 4470  
> queries/sec when results are served from query cache.

Peter seems to say that the "overhead" of using prepared statements is  
that they are 14.5% *faster* than using a non-prepared query  
execution, at least in this simple test.   I'd expect the relative  
difference probably diminishes with a more complex query or a larger  
result set.

In any case, we should be careful about citing round-trips as a  
significant performance factor, because it discourages people from  
using prepared queries when they should.

Regards,
Bill Karwin
Reply | Threaded
Open this post in threaded view
|

Re: PDO or Mysqli?

orrd
CONTENTS DELETED
The author has deleted this message.
Reply | Threaded
Open this post in threaded view
|

Re: PDO or Mysqli?

till
If performance is a concern, I'd recommend you do your own escaping and get a connection adapter:

// $db = Zend_Db_Adapter_foo
$actualDb = $db->getConnection()

That's what we usually do.

Till

--
till
Sent with Sparrow (http://www.sparrowmailapp.com/?sig)


On Monday, April 30, 2012 at 3:06 AM, orrd wrote:

>
> BillKarwin wrote
> >
> > Peter seems to say that the "overhead" of using prepared statements is
> > that they are 14.5% *faster* than using a non-prepared query
> > execution, at least in this simple test. I'd expect the relative
> > difference probably diminishes with a more complex query or a larger
> > result set.
> >
>
>
> That was only based on through-put for a large number of repeated identical
> queries that are only prepared once. The most real-world web servers,
> typically queries are only made once or a few times on a web page, and in
> that case there is a significant
> http://erlycoder.com/69/php-mysql-prepared-sql-statement-vs-sql-statement
> performance penalty to using prepared queries.
>
> --
> View this message in context: http://zend-framework-community.634137.n4.nabble.com/PDO-or-Mysqli-tp1556941p4597250.html
> Sent from the Zend Framework mailing list archive at Nabble.com (http://Nabble.com).
>
> --
> List: [hidden email] (mailto:[hidden email])
> Info: http://framework.zend.com/archives
> Unsubscribe: [hidden email] (mailto:[hidden email])
>
>