Imploding in PHP vs imploding in MySQL - which uses less cpu?

Question

Which of these options is more optimal?

imploding 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.

imploding 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);

Answer 1

You should probably know that the correct ("most optimal") choice is going to be a factor of many variables:

1. Your database and application hardware.
2. The size of your data
3. What load is already like on database and application servers.
4. Indexes and other things that may affect query execution plan on your dataset
5. How you actually want to use the data.
6. Network latency between app server and database server

But you can definitely analyze the program flow to help you arrive at an answer:

Imploding in PHP:

1. Execute a select query returning ALL friend IDs
2. Return every single ID to your application.
3. Build string in PHP as you read them from the result.

Pros:

• Flexibility in using raw IDs if you actually need them (meaning — if you go with creating the string in MySQL, but post-process it in PHP, the expensive post-process operations will likely negate benefit seen from bypassing PHP in the string-compilation stage)
• IDs could be (are?) returned to your application as numbers, not strings (1234567890 is 10 bytes ASCII, 4 bytes as a 32-bit integer)

Imploding in MySQL:

1. Execute an aggregate query
2. Build a string as it aggregates the results
3. Return one single string to your application
4. Output that string

Pros:

• Might use less memory on the database server for large datasets
• Can't think of much else. As soon as you need anything but a big concatenated string, this solution is definitely sub-optimal.