The method of time-based query Optimization in mysql

Editor to share with you the method of mysql query optimization by time, I hope you will gain a lot after reading this article, let's discuss it together!

Mysql time query optimization method: 1, [register_time] field is datetime type, converted to date matching, need to query out all rows for filtering; 2, you can use the [register_time] field to establish an index, the query is very fast.

Mysql query optimization method by 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 day 59 INTERVAL 59'). After reading this article, I believe you have some understanding of the time query optimization method of mysql. Want to know more about it. Welcome to follow the industry information channel. Thank you for your reading!