MySQL subquery usage example analysis

This article gives an example of the MySQL subquery usage. To share for your reference, the details are as follows:

Assume table my_tbl contains 3 fields a,b,c; You now need to query the minimum number of records for the column b in the table for each different value of the column a.

For example, the table records as follows:

a b c
1 3 'cd'
2 3 'nhd'
1 5 'bg'
2 6 'cds'
1 7 'kiy'
3 7 'vsd'
3 8 'ndf'

The desired results are:

a b c
1 3 'cd'
2 3 'nhd'
3 7 'vsd'

(1) One of the methods: first, find out the minimum value of b under each a value, and then query all records that meet the requirements according to these minimum values.

The sql that matches the minimum b value of the query is written as follows:

select A.* from my_tbl as A where A.b=(select min(b) from my_tbl as B where B.a=A.a);

Because of the nested query and intersection, it took me an hour to calculate the intermediate results in 800,000 records (I really doubt I made a mistake); It is not necessary to calculate the record quantity later.

(2) The above method is a disaster and can only be discarded.

The specific logic is as follows: first, group by columns a and b, and then select the record with the smallest value of column b in each group to generate the result set.

sql statement is written as follows:

select a,b,c,count(a) from (select a,b,c from my_tbl group by a,b) as A group by a;

After executing the query, it took only 1.1 seconds.

Again, it turns out that differences in sql's query strategies can directly lead to significant performance differences.

For more information about MySQL, please refer to MySQL Transaction Operation Skills Summary, MySQL Stored Procedure Skills Collection, MySQL Database Locking Skills Summary and MySQL Common Functions Summary.

I hope what I have described in this article will be helpful to you in the MySQL database.