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
– Bill Karwin, Commented Jan 12, 2023 at 15:24

Rick James · Accepted Answer · 2023-01-12 19:38:01Z

0

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?

answered Jan 12, 2023 at 19:38

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql select performance json_arrayagg vs two queries

case 1

case 2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

case 1

case 2

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related