PHP, PDO vs MYSQLi vs PHP (Insert)

Which is faster? Lets find it out.

First, my test machine is Windows 10 64bits and using PHP 7.1 32bits + Mysql 8 Beta.

What is the tested?. First, i have 3 tables. In each table, i will insert a new value. I will do the test many times per table, then i will repeat the whole test many times. What i am trying to benchmark? The use of different customers at the same time and how it impacts the performance.

Note: If you are inserting many values to the same table, then you could optimize the code, for example:

INSERT INTO table1(id,namecol)
 VALUES(551,’text1')
 ,(255,’text2');

instead of two insert but i am not testing a bulk insert.

The code:

for($ntry=0;$ntry<10;$ntry++) {
$conn2->query("truncate table table3");
$t1 = microtime(true);
for ($i = 0; $i < NUMTEST; $i++) {
$stmt = $conn2->prepare("INSERT INTO table3 (id, name) VALUES (:id, :name)");
$text = "hello";
$stmt->bindParam(':id', $i);
$stmt->bindParam(':name', $text);
$stmt->execute();
}
$t2 = microtime(true);
echo "pdo:" . ($t2 - $t1) . "<br>";


$conn->query("truncate table table2");
$t1 = microtime(true);
for ($i = 0; $i < NUMTEST; $i++) {
$idx = $i;
$text = "hello";
$sql = sprintf("INSERT INTO table2(id,name) VALUES('%s','%s')"
, mysqli_real_escape_string($conn, $idx), mysqli_real_escape_string($conn, $text));
$conn->query($sql);
}
$t2 = microtime(true);
echo "escape:" . ($t2 - $t1) . "<br>";

$conn->query("truncate table table1");
$t1 = microtime(true);
for ($i = 0; $i < NUMTEST; $i++) {
$queryPrepare = 'INSERT INTO table1(id,name) VALUES (?,?)';
$stmt = $conn->prepare($queryPrepare);
$idx = $i;
$text = "hello";
$stmt->bind_param('is', $idx, $text);
$stmt->execute();
}
$t2 = microtime(true);
echo "prepare:" . ($t2 - $t1) . "<br>";
}

And the result is:

pdo (prepared): 3.2009658813477 seconds.
php (escaped): 3.129753112793 seconds.
mysqli (prepared): 3.3415656089783 seconds.

(The error margin is around 10%)

Disappointment.

So, the difference between one way and another is practically nil. Its with 100 interactions.

So lets try with more interactions (5000 inserts)

pdo (prepared): 15.925825595856 seconds. (100 %)
php (escaped): 14.968855857849 seconds. (93.991082394776 %)
mysqli (prepared): 14.911539316177 seconds. (93.631185563515 %)

And again (the numbers are different, its the reason why the error margin)

pdo (prepared): 15.57869720459 seconds. (94.504699541801 %)
php (escaped): 16.316536903381 seconds. (98.980639867784 %)
mysqli (prepared): 16.484574079514 seconds. (100 %)

I added 4 new columns to the database and : (5000 inserts, 7 columns instead of 2)

pdo (prepared): 15.865914344788 seconds. (98.424458044026 %)
php (escaped): 16.119889974594 seconds. (100 %)
mysqli (prepared): 16.019695043564 seconds. (99.378439113492 %)

The results are practically the same.

I did the same running a query directly

$conn->query("truncate table table4");
$t1 = microtime(true);
for ($i = 0; $i < NUMTEST; $i++) {
$queryRaw = "INSERT INTO table4(id,namecol,namecol2,namecol3,namecol4,namecol5) VALUES ($i, '$text', '$text', '$text', '$text', '$text')";
$conn->query($queryRaw);
}
$t2 = microtime(true);
echo "prepare:" . ($t2 - $t1) . "<br>";
$raw+=($t2 - $t1);

pdo (prepared): 16.36131310463 seconds. (98.40254911655 %)
php (escaped): 16.351503372192 seconds. (98.34355002083 %)
mysqli (prepared): 16.626919984818 seconds. (100 %)
mysqli (raw): 15.716637372971 seconds. (94.525248135685 %)

And again

pdo (prepared): 15.101834058762 seconds. (94.078635054194 %)
php (escaped): 16.052352428436 seconds. (100 %)
mysqli (prepared): 15.574231863022 seconds. (97.021492223361 %)
mysqli (raw): 15.281609773636 seconds. (95.198568818891 %)

And the results are pretty much the same. May be its a slightly faster.

Conclusion:

The bottleneck is not the code, its the database.

Well…

But its not the entire conclusion. May be some way to connects to the database is faster than another but, if we consider the entire working pipeline, the database task is considerably slow, so the election of pdo, mysqli or php is a minor fraction of time.

PHP takes around 2% of the process time. Do you really want to optimize this 2%?.

So:

If you really want to optimize, then optimizing the database is more important than optimize the code (unless the code is doing unsolicited calls to the database).
One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.