Optimization of mysql query date by time, mysql Time query date

Optimization of mysql query date by time, mysql Time query date

For example, to query newly registered users yesterday, the statement is as follows:

EXPLAINselect * from chess_user u where DATE_FORMAT (u. register_time, '% Y-% m-% D') = '2017-01-25'; EXPLAINselect * from chess_user u where u. register_time BETWEEN '2017-01-25 00:00:00 'and '2017-01-25 23:59:59 ';

The register_time field is of the datetime type. If it is converted to a date and then matched, all rows need to be queried for filtering. In the second way, you can create an index on the register_time field to query extremely fast!

Attach date Conversion Function

DECLARE yt varchar (10); # DECLARE yt_bt varchar (19) yesterday; # DECLARE yt_et varchar (19) from yesterday ); # Yesterday's end time # SET the variable SET yt = DATE_FORMAT (DATE_ADD (now (), INTERVAL-1 day), '% Y-% m-% D '); SET yt_bt = DATE_FORMAT (DATE_ADD (now (), INTERVAL-1 day), '% Y-% m-% d 00:00:00 '); SET yt_et = DATE_FORMAT (DATE_ADD (now (), INTERVAL-1 day), '% Y-% m-% d 23:59:59 ');