background

There are two ways to query the total number of records in a subtable:

Select query

For example, common operations

Select *,(select count(表 1) from aa where id=t.id) CNT from test t;Copy the code

This does not seem to be a problem, but as the system runs for a long time, you may find that the SQL execution is slow and the front end will report a time out exception.

This code takes about 28 seconds to execute, which is….

I analyzed it, and the most time-consuming sentence in the whole SQL is this one

Because there is so much data in this table, a full query is bound to slow down the data.

2. LEFT JOIN mode

This is mainly the use of the left Join function count

After the transformation, the execution time is about 2 seconds, which is a big difference.

The above content is only used for recording knowledge, please point out any mistakes.