Geek Answers Handbook

Imploding in PHP versus imitation in MySQL - which uses less CPU?

Which of these parameters is more optimal?

embedding in MySQL

$rsFriends = $cnn->Execute('SELECT CAST(GROUP_CONCAT(id_friend) AS CHAR) AS friends FROM table_friend WHERE id_user = '.q($_SESSION['id_user'])); $friends = $rsFriends->fields['friends']; echo $friends;

vs.

imitation in PHP

 $rsFriends = $cnn->Execute('SELECT id_friend FROM table_friend WHERE id_user = '.q($_SESSION['id_user'])); while(!$rsFriends->EOF) { $friends[] = $rsFriends->fields['id_friend']; $rsFriends->MoveNext(); } echo implode(',',$friends);
+4
source share
3 answers

You should probably know that the correct (“most optimal”) choice will be a multiplier of many variables:

  • Your database and application hardware.
  • Size of your data
  • Which download is already similar to database and application servers.
  • Indexes and other factors that may affect the query plan in your dataset.
  • How do you really want to use data.
  • Network latency between application server and database server

But you can definitely analyze the program flow to help you get the answer:

PHP implantation :

  • Fulfilling a selection request that returns ALL friend identifiers.
  • Return each identifier to your application.
  • String string in PHP when you read them from the result.

Pros:

  • Flexibility in using raw identifiers if you really need them (which means "mdash", if you go with creating a string in MySQL but post-process it in PHP, costly post-processing operations most likely negate the benefits observed when PHP is bypassed to the stage of compiling the line)
  • Identifiers can be (is there?) Returned to your application as numbers, not strings ( 1234567890 - 10 ASCII bytes, 4 bytes as a 32-bit integer)

MySQL implantation :

  • Aggregate Query Execution
  • Build row as results merge
  • Return one line to your application
  • Print this line

Pros:

  • May require less memory on the database server for large datasets.
  • I can’t think of many other things. As soon as you need something other than a large concatenated string, this solution is certainly not optimal.
+4
source

In the comments, the optimal value is largely contextual. With that said, my philosophy is this: if the database can do it, let it be.

In particular, for your case, if you do not analyze or manipulate the results - this means that it is strictly for combining all records for output - then I will definitely vote for the database.

+3
source

What is probably worth optimizing here is the developer’s time and efforts, for implementation and maintenance. The difference in CPU cycles (as part of the overall work performed) is most likely trivial. I suggest you do this no matter how you can write, test, and maintain most easily.

+1
source

More articles:


All Articles