Questions about about PDO & MySQL best practices

I have many questions about PDO ...

  1. Should I use prepare() only when I have parameters to bind? When I need to do a simple query like select * from table order by ... should i use query()?

  2. Should I use exec() when I have update and delete operations and need to get the number of rows affected, or should I use PDOStatement->rowCount() instead?

  3. Should I use closeCursor when I do insert, update and delete, or only with select when I need to do another select?

  4. Does $con = NULL; really close the connection?

  5. Is using bindParam with foreach to make multiple inserts a good point? I mean performance wise, because I think that doing (...),(...) on the same insert is better isn't it?

  6. Can you provide me some more information (URL) about performance points when using PHP PDO MySQL? If someone has another hint it would be really useful.

Answers


  1. When I was developing the DB layer in Zend Framework 1.0, I made it use prepare/execute for all queries by default. There is little downside to doing this.* There's a little bit of overhead on the PHP side, but on the MySQL side, prepared queries are actually faster.

  2. My practice is to use query() for all types of queries, and call rowCount() after updates. You can also call SELECTROW_COUNT().

  3. CloseCursor is useful in MySQL if you have pending rows from a result set, or pending result sets in a multi-result set query. It's not necessary when you use INSERT, UPDATE, DELETE.

  4. The PDO_mysql test suite closes connections with $con=NULL and that is the proper way. This won't actually close persistent connections managed by libmysqlnd, but that's deliberate.

  5. Executing a prepared INSERT statement one row at a time is not as fast as executing a single INSERT with multiple tuples. But the difference is pretty small. If you have a large number of rows to insert, and performance is important, you should really use LOAD DATA LOCAL INFILE. See also http://dev.mysql.com/doc/refman/5.6/en/insert-speed.html for other tips.

  6. You can google for "PDO MySQL benchmark" (for example) to find various results. The bottom line, however, is that choosing PDO vs. Mysqli has no clear winner. The difference is slight enough that it diminishes relative to other more important optimization techniques, such as choosing the right indexes, making sure indexes fit in RAM, and clever use of application-side caching.


* Some statements cannot run as prepared statements in MySQL, but the list of such statements gets smaller with each major release. If you're still using an ancient version of MySQL that can't run certain statements with prepare(), then you should have upgraded years ago!


Re your comment:

Yes, using query parameters (e.g. with bindValue() and bindParam()) is considered the best methods for defending against SQL injections in most cases.

Note that there's an easier way to use query parameters with PDO -- you can just pass an array to execute() so you don't have to bother with bindValue() or bindParam():

$sql = "SELECT * FROM MyTable WHERE name = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute( array("Bill") );

You can also use named parameters this way:

$sql = "SELECT * FROM MyTable WHERE name = :name";
$stmt = $pdo->prepare($sql);
$stmt->execute( array(":name" => "Bill") );

Using quote() and then interpolating the result into a query is also a good way to protect against SQL injection, but IMHO makes code harder to read and maintain, because you're always trying to figure out if you have closed your quotes and put your dots in the right place. It's much easier to use parameter placeholders and then pass parameters.

You can read more about SQL injection defense in my presentation, SQL Injection Myths and Fallacies.


Most of questions can be answered with just common sense. So, here I am.

  1. It doesn't matter actually.

  2. Abolutely and definitely - NO. Exec doesn't utilize prepared statements. That's all.

  3. Doesn't really matter. If you ever need this, your program architecture is probably wrong.

  4. You can easily test it yourself. A personal experience is always preferred.

  5. The difference considered to be negligible. However, if your multiple inserts being really slow (on INNODB with default settings for example) you have to use a transaction, which will make them fast again.

  6. There is NONE. PDO is just an API. But APIs aren't related to performance. They just translate your commands to the service. It's either your commands or service may affect performance, but not mere API.

So, the rule of thumb is:

it's query itself that affects performance, not the way you are running it.

Need Your Help

Eclipse EGit pushing latest commit hangs plink.exe

eclipse github raspberry-pi putty egit

Yesterday I setup EGit and PuTTY so I can share my latest changes to the code to my RPi which then packs my binary into a executable jar, and with quiet alot of extra work, I got it working to some

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.