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.

Measuring it yourself, or your server, with your data, is the only meaningful answer. There are too many factors to make a general statement. — Bill Karwin, Jan 12 '23 at 15:24

score 0 · Answer 1 · answered Jan 12 '23 at 19:38

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?

mysql select performance json_arrayagg vs two queries

case 1

case 2

1 Answers1