Mysql Query the most recent record sql statement of optimization section

Bad policy-after searching out the results, sort the time and take the first item

select * from a 
where create_time<="2017-03-29 19:30:36" 
order by create_time desc
limit 1

Although this can take out the latest record at the current time, it needs to traverse the table once for one query, which will take time to query more than one million data; limit is to take out all the results first, and then take the first article, which is equivalent to occupying unnecessary time and space in the query; In addition, if it is necessary to take out the latest record in batches, for example, "an order table with users, order time and amount, and it is necessary to query the latest order record of all users once", then each user will have to traverse the whole table once once. In the case of large data, the time will increase exponentially and cannot be put into actual use.

China Policy-group by after query sorting

select * from (
  select * from a
  where create_time<="2017-03-29 19:30:36" 
  order by create_time desc
)  group by user_id

Later, it was found that group by can be grouped according to the parameter column of group by, but only one result was returned. After careful observation, it was found that group by returned the first record after grouping. Time is arranged in sequence by default after query, so it is necessary to arrange the time in reverse order before taking out the nearest one.

In this way, the query is actually carried out twice. Although the time is a qualitative leap compared with the first method, it can be optimized one step further.

Best policy-combine max () method with group by

select *,max(create_time) from a
where create_time<="2017-03-29 19:30:36" 
group by user_id

This sentence can be understood as grouping the result sets according to user_id, and each group takes the maximum record of time. In this way, we can query the most recent records in batches, and only need to traverse the table once, even in the case of huge data, we can find out the results in a short time.

Extension:

There is now one asset equipment table: base_assets_turn

Query the latest custodian of assets

Note: Assume asset number ASSETS_ID=254

Bad strategy:

select * from base_assets_turn 
where ASSETS_ID = 254
order by create_time desc
limit 1

Zhongce:

select * from ( select * from base_assets_turn 
where ASSETS_ID = 254
order by create_time desc) tt GROUP BY tt.ASSETS_ID;

Best policy:

So how to write the best policy? Welcome to leave a message!

The above is the site to you Mysql query the latest record of sql statement (optimization), I hope to help you, if you have any questions welcome to leave me a message!