MySQL – How to Execute Subquery Only Once

MySQLperformancequery-performance

How do I execute a Subquery once in a Query like this

http://sqlfiddle.com/#!9/8b149d/1/0

select 
id, 
(
  select group_concat(user) 
  from table_b
) as subquery 
from table_a

to maintain performance of the Query, Or it is better If I run two Queries separatly?

I want a result as if I used

select group_concat(user) from talbe_b;
select id from table_a;

but in a single query.

Best Answer

If you want a single result set with the first row containing a list of all users from table_a, and subsequent rows listing all ids from table_b (one per row), you could UNION the two queries. However, you would have to cast the results to a common data type (presumably something like varchar), and you would technically need to include a sort order field to guarantee that the users are in the first row.

SELECT values
FROM (select group_concat(user) as values, 1 as sort_order from table_b
      UNION ALL
      select CAST(id as varchar) as values, 2 as sort_order from table_a
     ) sq
ORDER BY sort_order
;

This will almost certainly be at least slightly slower than running the two queries separately. Not sure if that will matter, in your circumstance.

(Note: UNION checks for and eliminates duplicate rows; UNION ALL just combines all rows without checking. Since we don't need to worry about duplicates....)

Related Question