-1

Both codes does the same Job, But which one is better to use and when to use?

PHP method

$names = [];
$Query = "SELECT DISTINCT name FROM names";
$stmt = $conn->prepare($Query);
$stmt->execute();
while ($name = $stmt->fetch()) {
    $names[] = $name['epic'];
}
$names = implode(',', $names);

SQL method

$Query = "SELECT GROUP_CONCAT(DISTINCT name) AS names FROM names";
$stmt = $conn->prepare($Query);
$stmt->execute();
$row = $stmt->fetch();
$names = $row['names'];
Toleo
  • 764
  • 1
  • 5
  • 19

1 Answers1

2

It depends. GROUP_CONCAT() has the limit which is imposed by group_concat_max_len system option, and its length is 1024 by default (more info).

Also, it will concatenate non-null values, which you may want to handle in a different way, rather than just ignoring them.

Dan Beaulieu
  • 19,406
  • 19
  • 101
  • 135
Oliver Maksimovic
  • 3,204
  • 3
  • 28
  • 44
  • As i read, I can increase this `group_concat_max_len` as i want, But in case i set the limit to `999999`, Wouldn't that have some effect on the Database? – Toleo Dec 26 '17 at 00:18
  • @Toleo well, it's also constrained by `max_allowed_packet`, which _could_ have an effect. See here for more: https://stackoverflow.com/a/5019936/197487 https://reformatcode.com/code/mysql/is-increasing-groupconcatmaxlen-to-500000-will-cause-a-performance-issue – Oliver Maksimovic Dec 26 '17 at 00:22
  • In case i needed to increase `group_concat_max_len` to `200,000`, Which method would you recommend? the `SQL` or `PHP`? – Toleo Dec 26 '17 at 00:30
  • 1
    @Toleo never ask an imaginary question – Your Common Sense Dec 26 '17 at 05:58
  • @YourCommonSense, tbh, i don't get where is the imaginary part in my question,Is it the increasing to `200,000`? If yes, then i should use `PHP` method to be more future proof, Or is it bad if i set it globally and better if i set `200,000` per session only? – Toleo Dec 26 '17 at 14:31
  • @Toleo okay, what would be the use of the 200,000 comma separated names then? – Your Common Sense Dec 26 '17 at 15:00
  • @YourCommonSense I want a full serialized `Id` list, [1, 2, 3, 4, 5, 6 ....] to 50,000 `id` at a single variable, it would require `200,000` at least. – Toleo Dec 26 '17 at 16:13
  • @Toleo 1. ugly comma-separation is anything but serialization. 2. 50,000 id will make 50,000 elements not 200,000. 3. You failed to answer the question **what would be the use of the 200,000 comma separated names**, or 50,000 ids. – Your Common Sense Dec 26 '17 at 17:19
  • @YourCommonSense 1.Select all distincted `id`s or `names`. 2. Put it inside `array()`. 3. Make it as Autocomplete list for Search Input. – Toleo Dec 26 '17 at 17:22
  • 1
    @Toleo WAT? Autocomplete consists of 200,000 entries? It will make megabytes to send! Just as usual, a weird request reveals lack of knowledge, you should learn what autocomplete is and how it is implemented. – Your Common Sense Dec 26 '17 at 17:52
  • @YourCommonSense I understand it would be a heavy dictionary search, And that i can just use AJAX to get the data part by part, But still want to make it as an experiment, But i still wonder if `50,000` `group_concat` is worth it or PHP is better for this job. – Toleo Dec 26 '17 at 18:05
  • 1
    @Toleo it makes absolutely no sense to create a string consists of 50,000 comma separated values in the first place. The sooner you'll understand this the less time you'll waste. DO you really think that a user would scroll down 50,000 suggestions? What are you thinking about? – Your Common Sense Dec 26 '17 at 19:21
  • @YourCommonSense Nope, Not the User, But me, As i said, I use it as an experiment to check why is it bad, how much does a `50,000` word is worth on the `RAM` and `CPU` to be read, Just static for me to understand the limits of something. – Toleo Dec 26 '17 at 19:38
  • So, your question is indeed an imaginery one, not a real question. So, it is kinda pointless. With a few records, you will not see significant difference between the two approaches. If you have lots of records to be conactenated, then you probably have a design problem. But if you are so curious, then just simply test it yourself. – Shadow Dec 27 '17 at 00:18