mysql select performance json_arrayagg vs two queries

Question

in mysql 8.0.23

case 1

select *
from boards b
where b.id = 11

select *
from comments c 
where c.board_id = 11

case 2

select b.*
     , c.comments
from boards b 
left outer join lateral (
  select json_arrayagg(json_object(
    'id', c.id,
    'title', c.title,
    ...
  )) as comments
  from comments c 
  where c.board_id = b.id
) c on true
where b.id = 11

Which one is more performant?

I'm not curious about anti-patterns or anything like that.

Is it better to reduce the connection cost and fetch it all at once?

Or is it better to have no json conversion cost

Case 2 was faster in my tests, but it only confirmed the response speed of the query. I want to know what mysql server can process more queries in same time.

Answer 1

Also test this for 'performance':

( SELECT 'b', JSON_ARRAYAGG(...)  FROM b WHERE b.id=11 )
UNION ALL
( SELECT 'c', JSON_ARRAYAGG(...)  FROM c WHERE c.id=11 )
;

or SELECT ( SELECT JSON_ARRAYAGG(...) FROM b WHERE b.id=11 ) AS bbb ( SELECT JSON_ARRAYAGG(...) FROM c WHERE c.id=11 ) AS ccc ;

Each of these would like your second version in that they avoid a second roundtrip to the server. The roundtrip is a non-trivial part of the total time.

If b and c have the same columns (which is usually a bad schema design), then

SELECT b.*, c.*  -- or spell out the columns
    FROM b
    JOIN c
    WHERE b.id = 11
      AND c.id = 11

Is it coincidence or deliberate that the two ids are tested against the same value?