Which of these 3 PDO codes has the best performance?

Asked

Viewed 256 times

-1

Purpose:

Select a single line with LIMIT 1

Internal Use:

The selects receive only internal parameters

You don’t need PREPARE because there is no external user data

$pdo = new PDO("mysql:host=A.com;dbname=B; charset=utf8", "C", "PW");

exemplo 1:
$con = $pdo->query("SELECT id,titulo FROM cursos WHERE cod=1 LIMIT 1");
foreach($con as $row) {
echo $row['id'].' - '.$row['titulo']; 
}

Example 2:

$con = $pdo->query("SELECT id,titulo FROM cursos WHERE cod=1 LIMIT 1");
$con->execute();
$sel=$con->fetch();
echo $sel['id'].' - '.$sel['titulo'];

Example 3:

$con = $pdo->query("SELECT id,titulo FROM cursos WHERE cod=1 LIMIT 1")->fetch();
echo $con['id'].' - '.$con['titulo'];

Selected row contains 80 columns (with or without data)

The 3 work OK, but I have doubts which to use.

If there is another way, shorter or faster.

php pdo performance

  • There are no changes in Mysql, only in PHP. Note that the SQL passed to the DBMS is the same. The question then becomes "how PDO reads more performatively".

  • 1

    I’d say you measure the performance of each of the three ways. I don’t have a basis on PHP/PDO to speak properly, but you could test it on a significant mass of data to see how performative each one is. Repeat a few hundred times, take the mean and standard deviation and use what you find best (usually lower average and with lower deviation)

  • Do you recommend changing the text of the question? Yours is more objective? I’m learning to use the Stack, I appreciate the attention

  • I said more to make you realize what the point is. Not so much about the use of the site, but so you have the right idea of what is happening. The world gets a lot easier and more direct when you know what you’re dealing with. On the use of Stackoverflow, I’ve seen better texts. I recommend editing the title, since there is no real difference between selects, but in recovering the data in PHP, but also I don’t have a better title suggestion. What bothered me the most was the tag I already replaced

  • 3

    If it returns only one record, I see no reason to use the foreach, discarding example 1. Between examples 2 and 3, the only difference I saw was the execute, but how does it work without example 3, it is really needed in example 2? If it is not, the examples will be equal.

  • @Geo Did the answer solve your question? Do you think you can accept it? See [tour] if you don’t know how to do it. This would help a lot to indicate that the solution was useful to you. You can also vote on any question or answer you find useful on the entire site

Show 1 more comment

1 answer

6

If performance is relevant to your application use a compiled static typing language and preferably use the zero-cost abstraction philosophy. Type C, C++, Rust, eventually C#, Kotlin, Java, D, Go, Swift. Do not use a script. For PHP performance is irrelevant.

The highest cost there, and with a wide margin of difference, probably some orders of magnitude, is the database query, so if you need better performance think about cache strategy or rearquitting the application, the gains usually come more than design than details in the code.

If you still want some execution gain, leave the PDO. Almost nobody needs it and it costs a lot more than direct access to the database client.

That being said, the third is simpler, straightforward and even by directly performing fewer things is probably the fastest (it will not be easy to measure the difference in performance). I say probably because only knowing the implementation of PDO to know, but I only used the word so it doesn’t seem that the mere fact of having one command less already makes everything faster, it may be that the lack of it generates other consequences that makes it slower, but in this case it is highly unlikely, and if it happens flee the PDO.

Then something like that would be better:

$dados = $mysql->query("SELECT id, titulo FROM cursos WHERE cod = 1")->fetch();
echo $dados['id'] . ' - ' . $dados['titulo'];

I put in the Github for future reference.

But depending on where you use it there may be gains doing otherwise. You can’t analyze performance without context.

There is how to improve more by doing something totally different from this, but only in the specific case with a lot of information available can you know what.

And working is different from being right. Not that this is wrong, I have no way to state without context, but the alert.

Fiat 147 todo detonado andando pelas ruas

  • I liked your suggestion and tested it. But the result doesn’t look like it. The connection is OK. Did I make a mistake? $mysql = new mysqli('h','A','Pw','B') or die ("Failed"); if (!$mysql){echo'Error'. mysqli_error();Exit;}Else{echo'Connected';} $data = $mysql->query("SELECT id,title FROM courses Where Cod=1")->fetch();echo $data['id'] . ' - ' . $data['title'];

  • Then it’s another problem, ask another question with enough details about it.

  • Ok, (there is no more detail, it’s all in the comment above) I appreciate the explanation and analogy with cars. For me: Ferrari = Performance with quality. Thank you.

  • @Geo Sem execute will not work.

  • Pairing with the 147 :D

  • 1

    I’ll change my nickname to "Sponge", because any little time I’m always absorbing content ! It deserves emphasis: "If you still want some execution gain leave the PDO. Almost nobody needs it and it costs a lot more than direct access to the database client."

Show 1 more comment

Browser other questions tagged php pdo performance

You are not signed in. Login or sign up in order to post.