group by and group_concat alternative

Question

Using GROUP_CONCAT() usually invokes the group-by logic and creates temporary tables, which are usually a big negative for performance. Sometimes you can add the right index to avoid the temp table in a group-by query, but not in every case.

https://stackoverflow.com/a/26225148/9685125

After Reading this post, I realized that I was doing wrong, because many time I made complicated query using huge GROUP_CONCAT(). Such as

GROUP_CONCAT(DISTINCT exam.title) AS exam,
GROUP_CONCAT(subject.title, '<br/> Th - ', mark.th, ' | PR - ', mark.pr SEPARATOR ',') AS mark

But what can be alternative of GROUP_CONCAT in following situation without using subquery. I mean using only Mysql join,

For example, let see two relational database and and query to explain my problem

Student

id  |   Rid |   name
========================
1   |   1   |   john

Marks

id  |   std_id  |   th
======================
1   |   1       |   60
2   |   1       |   70
3   |   1       |   80
4   |   1       |   90

"SELECT 
    student.en_name, mark.th 
    FROM student
    JOIN mark ON student.id = mark.std_id
    WHERE student.id=:id;"

Column would be repeated if only use JOIN

John: 60, John: 70, John: 80, John: 90

So, I use GROUP BY. But if I assign GROUP BY to student.id, only first row is fetched

"SELECT 
    student.en_name, mark.th 
    FROM student
    JOIN mark ON student.id = mark.std_id
    WHERE student.id=:id
    GROUP BY student.id;"

Result is
John: 60

So to get result, we have to assign that group.concat

"SELECT 
    student.en_name,
    GROUP_CONCAT(mark.th) as mark 
    FROM student
    JOIN mark ON student.id = mark.std_id
    WHERE student.id=:id
    GROUP BY student.id;"

And final and expected result using exploding array

$name=$row['en_name'];
echo "$name: <br/>";
$mrk_array = explode(',',$row['mark']); 
foreach($mrk_array as $mark){
    echo $mark.", ";
}

John:
60, 70, 80, 90,

Here, I don't see any alternative of GROUP_CONCAT to fetch all associated value of each Id and prevent duplicate, please help me how to replace GROUP_CONCAT from here.

Also, one friend told me

So why GROUP_CONCAT if you're "exploding" it. You might as well return a nice associative array and then deal with displaying it there.

But I can't understand, what he means ?

score 2 · Accepted Answer · answered Sep 01 '18 at 02:11

Too long for a comment...

With your original query, you are effectively returning an array of rows (associative arrays):

array(array('en_name' => 'John', 'mark' => 60),
      array('en_name' => 'John', 'mark' => 70),
      array('en_name' => 'John', 'mark' => 80),
      array('en_name' => 'John', 'mark' => 90)
     )

When you use GROUP BY and GROUP CONCAT, you are effectively imploding the 'mark' elements of that array to

array('en_name => 'John', 'mark' => '60,70,80,90')

and you then have to explode the array again to process the data.

If you stick with your original query, you can instead do the imploding in your application framework e.g.

$name = "";
while ($row = $result->fetch()) {
    if ($row['en_name'] != $name) {
        $name = $row['en_name'];
        echo "$name: <br/>" . $row['mark'];
    }
    else {
        echo ',' . $row['mark'];
    }
}

Output:

John: 
60,70,80,90

This will generally be a lot faster than using GROUP BY and GROUP_CONCAT in the database.

group by and group_concat alternative

1 Answers1