Optimization skills of Mysql querying dates according to time

For example, the query of newly registered users yesterday can be written in the following two ways:

EXPLAINselect * from chess_user u where DATE_FORMAT (u.registerless timekeeping% YMY% mMUE% d') = '2018-01-25 from chess_user u where u.register_time BETWEEN' 2018-01-25 0000VV 00' and '2018-01-25 23V 59from chess_user 59'

The register_time field is of type datetime. If it is converted to date matching, all rows need to be queried and filtered. And the second way of writing, you can use to build an index on the register_time field, the query is very fast!

Attached date conversion function

DECLARE yt varchar (10); # yesterday's DECLARE yt_bt varchar (19); # yesterday's start time DECLARE yt_et varchar (19); # yesterday's end time # set the variable SET yt=DATE_FORMAT (DATE_ADD (now (), INTERVAL-1 day),'% Ymuri% mmi% d'); SET yt_bt=DATE_FORMAT (DATE_ADD (now (), INTERVAL-1 day),'% Y-%m-%d 00lv 0000') SET yt_et=DATE_FORMAT (DATE_ADD (now (), INTERVAL-1 day),'% Y-%m-%d 23 now 59')

Summary

The above is the editor to introduce to you the Mysql query date according to time optimization skills, I hope to help you, if you have any questions, please leave me a message, the editor will reply to you in time. Thank you very much for your support to the website!