In PDO is there a difference in performance if a int is quoted as a string?

Question

Is there a difference in performance if an integer is bound as a string in a prepared PDO query? In Mysql the queries work either if the value is bound as an int or a string, but is there any difference in the performance, or any pitfalls when doing that?

$pdo = new PDO(
    "mysql:host={$host};port={$port};dbname={$dbname};charset=utf8", 
    $username, 
    $password
);
$statement = $pdo->prepare("SELECT * FROM `table` WHERE `id` = :param");

// Is there any performance difference between the two rows below
$statement->bindValue(":param", 5);
$statement->bindValue(":param", 5, PDO::PARAM_INT);

$statement->execute();

Is there any difference between binding a parameter and specifying its type, or just quoting it as a string?

Don't know about mysql, but if for example you decide to use oracle as a database you will get errors. — Daan, May 12 '15 at 07:19
Not related to performance but one other difference would be that if you want to bind the offset and number of rows for a LIMIT statement you need to use them as integers — mishu, May 12 '15 at 07:21
Take a look at - MySQL/PDO::quote(), Putting single quotes around integers — Sougata Bose, May 12 '15 at 07:23
My guess is that if you don't specify the type of the value, PDO will guess the type by itself, if this is the case then there is an extra piece of code that runs if you don't specify the type. Just a guess. — Fadey, May 12 '15 at 07:23

Rizier123 · Accepted Answer · 2015-05-12T07:32:40.157

If you want to take it exactly the method where you specify the type is slightly faster than where you not specify the type and the default type is PDO::PARAM_STR.

If you run it 1 million time the avarage is as followed:

int type specified: 0.53 seconds ($stmt->bindValue(":param", 5, PDO::PARAM_INT);)
no type specified: 0.66 seconds ($stmt->bindValue(":param", 5);)
str type sepcified: 0.70 seconds ($stmt->bindValue(":param", 5, PDO::PARAM_STR);)

_{Tested with PHP version 5.6.3; 32bit Windows;}

score 0 · Answer 2 · answered May 12 '15 at 07:30

In my experience, there is no such performance different. MySQL automatically will convert string to number (in depends on column type).

If I want to be precise, PDO::PARAM_STR is more slowly then PDO::PARAM_INT. PDO::PARAM_STR is by default.

It's because the server have to check your data.

In PDO is there a difference in performance if a int is quoted as a string?

2 Answers2