Jump to content

Photo

Database extension (MySQL, MySQLi, PDO) benchmarks

- - - - -

  • Please log in to reply
3 replies to this topic

#1
Daniel15

Daniel15

    dan.cx

  • Moderators
  • 3744 posts
  • Joined: 17-April 10
  • LocationMountain View, CA
  • Expertise:HTML, CSS, PHP, Java, Javascript, Node.js, SQL
In this topic, I mentioned doing benchmarks of using prepared statements vs stored procedures vs "regular" queries. I decided to benchmark a very simple query: SELECT * FROM products WHERE date > "2011-01-01" (schema and data is tiny, and available in code below). Using a simple query like this, we can see the overheads of the database extension being used, as the actual execution won't be a significant factor in the speed. I reused the benchmarking script I've posted here previously.

Setup:
- Ubuntu 11.10 64-bit
- Intel Core i5 2400 (3.1 GHz)
- 4 GB RAM
- PHP 5.3.6 and MySQL 5.1.58

And... In the end, it doesn't really matter. As long as you don't use mysqli and prepare your queries every single time in a loop. Here is a pretty graph with all the benchmarks I did:
Posted Image

Notice there's a single benchmark that's a lot higher than the others. When you remove the "mysqli_prepare_every_time" line, the difference between the fastest and the slowest method is a lot smaller. Here's a graph of that:
Posted Image

Notice how close together they are? The difference between fastest and slowest around 100ms for 14,000 queries. In the end, this isn't going to be the bottleneck in your application. The bottleneck will be the query that is being run, and not the PHP extension used to run it. So you may as well use PDO and prepared statements because they're awesome :P.

I didn't include the results from using stored procedures, as they were quite a bit higher than prepared statements. I'm too tired to do it now, but feel free to edit these benchmarks to benchmark a more complex query. The code is available here. My assumption is that with a more complex query, using stored procedures and prepared statements that are reused will have a much bigger impact and definitely beat regular queries.

Edit: Just realised I forgot to label my graphs <_<. The X-axis is iterations and the y-axis is time in milliseconds.
Daniel15! :D
Posted Image

Repeat after me: jQuery is not JavaScript. It is not the answer to every JavaScript-related question. When you have to write some JavaScript, do not instantly react with "Oh, I'll do that with jQuery!"

Spoiler

#2
Cyril

Cyril
  • Members
  • 2561 posts
  • Joined: 03-August 10
  • Expertise:HTML, CSS, PHP, Javascript, Graphics
Well then again -- the main reason I changed (and the guys where I did my internship after I showed them xD) is the security. PDO vs mysql_ ;)


website :: github :: twitter :: dribbble :: forrst
html, css, php, javascript, graphics


#3
Daniel15

Daniel15

    dan.cx

  • Moderators
  • 3744 posts
  • Joined: 17-April 10
  • LocationMountain View, CA
  • Expertise:HTML, CSS, PHP, Java, Javascript, Node.js, SQL
That and mysql_ is deprecated whereas PDO is not. And if you use a (relatively lightweight) abstraction layer like a query builder, you can easily switch between database systems.
Daniel15! :D
Posted Image

Repeat after me: jQuery is not JavaScript. It is not the answer to every JavaScript-related question. When you have to write some JavaScript, do not instantly react with "Oh, I'll do that with jQuery!"

Spoiler

#4
NoizeMe

NoizeMe
  • Members
  • 591 posts
  • Joined: 06-May 10
  • LocationGermany
  • Expertise:HTML, CSS, PHP, Java, Javascript, Python, Node.js, SQL, MongoDB, CouchDB, Cassandra

That and mysql_ is deprecated whereas PDO is not. And if you use a (relatively lightweight) abstraction layer like a query builder, you can easily switch between database systems.


But query builders are, if not used at compile-time a significant overhead ;).

Posted Image





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users