Mysql query the most recent record of the sql statement (optimization)

The worst option is to sort the results by time and take the first one.

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

Although this method can retrieve the most recent record at the current time, it requires traversing the table once for each query, which will be time-consuming for queries with more than one million data points; limit retrieves all results first and then the first one, which is equivalent to taking up unnecessary time and space in the query; and if you need to retrieve the most recent record in batches, for example: "an order table has users, order times, and amounts, and you need to query the most recent order records of all users at one time", then each user's query will require traversing the entire table once. When the data is large, the time will increase exponentially and cannot be put into practical use.

Middle strategy - query sorted and group by

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

Later I found that using group by can group by the parameter column of group by, but only one result is returned. After careful observation, I found that group by returns the first record after grouping. By default, the time is sorted in order after querying, so you need to sort the time in reverse order first to retrieve the one closest to the current one.

This query actually performs two queries. Although the time is much faster than the first method, it can be further optimized.

The best strategy is to use the max() method in combination 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 set according to user_id, and taking the record with the maximum time in each group. This makes it possible to query the latest records in batches, and only requires traversing the table once, so that the results can be found in a very short time even when the amount of data is huge.

Extensions:

Now there is an asset equipment table: base_assets_turn

Query the latest custodian of assets

Note: Assume that the asset number ASSETS_ID=254

The next best option:

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

Middle policy:

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

Best strategy:

So how should the best strategy be written? Welcome to leave a message!

The above is the MySQL SQL statement for querying the most recent record (optimization) brought to you by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message!