1

As far as I understand, using or should be avoided as much as possible as rule of thumb in query.

I have two tables having one-to-many relationship.

And I have joined two tables like below.

SELECT A.*, group_concat(B.info separator ','), group_concat(B.info2 separator ',')
FROM A
JOIN B
ON A.id = B.a_id
GROUP BY A.id;

Does using GROUP_CONCAT affect the performance like or does in MySQL? If it does, what would be the efficient way to join those tables?

Any suggestion or advice would be appreciated. Thank you.

  • Is id the PRIMARY KEY of A? But surely not of B?? – Rick James Jan 25 '18 at 17:59
1

So, the GROUP_CONCAT is an additional work for the MySQL involving strings, so you should expect some slow down, but rest assured it will not kill the performance of the MySQL.

To join those tables, you can use those JOINS commands that the MySQL had. Like INNER JOIN, FULL JOIN, etc. Take a look at this https://www.tutorialspoint.com/mysql/mysql-using-joins.htm

  • Welcome to the site, we are glad to have you! This answer does not address the OP's question though, could you expand on it to make it specific to what is being asked? – Mr.Brownstone Jan 25 '18 at 3:52
  • Does it make sense now? – Database Admin Jan 25 '18 at 3:57
  • so if I were to put indexes, which fields should be indexed? Is it wise to index info and info2 fields? – smchae Jan 25 '18 at 5:29
  • @smchae - Please provide SHOW CREATE TABLE` for both tables; the indexing question would be answered wrongly if, for example, info2 were TEXT. – Rick James Jan 25 '18 at 18:01
4

Your interests are generally not well-served by learning somewhat vague "rules" like "OR should be avoided" or "group_concat is slow."

Neither is actually correct, they only have elements of truth underlying them.

Use the correct capability to accomplish your purpose, while understanding that sometimes your interests are served by modifying your purpose to accommodate inherent limitations.

Using GROUP BY (not simply GROUP_CONCAT) can cause a performance hit if the optimizer has no effective way to accommodate the grouping... but this has nothing to do with whether GROUP BY should be used. If you need it, then of course you should use it... but manipulating strings is a cake-walk compared to scanning disks and sorting without an index, and the CPU cycles consumed by the concatenation are near the noise floor.

The take-away here is to understand how the optimizer sees your query, and how it plans to resolve it. Using EXPLAIN SELECT ... to verify that the server has made sensible choices (and that you have written a sensible query) should be second-nature to you if you want good performance.

https://dev.mysql.com/doc/refman/5.7/en/explain.html#explain-execution-plan

Interpreting the output can seem like a bit of a dark art, but you'll find it's worth the effort.

  • where is the element of truth for the OR being slow? – Evan Carroll Jan 31 '18 at 4:22
  • @EvanCarroll the element (nugget, grain, hint) of truth is that something like where foo = 6 or bar = 9 or buzz = 'fizz' may be difficult or less likely to for the optimizer to plan well, since OR doesn't short-circuit to false on any false value the way AND does... thus a candidate for an unanticipated full table scan. When there are usable indexes for each column, the index merge optimizations help with this, but the optimizer doesn't always select them. – Michael - sqlbot Jan 31 '18 at 20:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.