MySQL optimization based on time query date

For example, to query the new registered users yesterday, the following two types of writing:

EXPLAINSelect *  fromChess_user uwhereDate_format (U.register_time,'%y-%m-%d')='2018-01-25'; EXPLAINSelect *  fromChess_user uwhereU.register_timebetween '2018-01-25 00:00:00'  and '2018-01-25 23:59:59';

The Register_time field is a DateTime type and is converted to date to match, and all rows need to be queried for filtering. And the second way, you can use the Register_time field to build an index, query extremely fast!

Attach Date conversion function

DECLAREYtvarchar(Ten); #昨天DECLAREYt_btvarchar( +); #昨天开始时间DECLAREYt_etvarchar( +); #昨天结束时间 #设置变量SETYt=Date_format (Date_add (now (), INTERVAL-1  Day),'%y-%m-%d'); SETYt_bt=Date_format (Date_add (now (), INTERVAL-1  Day),'%y-%m-%d 00:00:00'); SETYt_et=Date_format (Date_add (now (), INTERVAL-1  Day),'%y-%m-%d 23:59:59');

MySQL optimization based on time query date