How to optimize mysql query by time

Mysql query optimization method by time: 1. The [register_time] field is of datetime type. When converted to date and then matched, all rows need to be queried for filtering; 2. You can use the [register_time] field to create an index , the query is extremely fast.

The operating environment of this tutorial: windows7 system, mysql8.0.22 version, thinkpad t480 computer.

More related free learning recommendations: mysql tutorial(Video)

Mysql query optimization method by time:

For example, to query the newly registered users yesterday, there are two ways to write:

EXPLAIN
select * from chess_user u where DATE_FORMAT(u.register_time,'%Y-%m-%d')='2018-01-25';

EXPLAIN
select * from chess_user u where u.register_time BETWEEN '2018-01-25 00:00:00' and '2018-01-25 23:59:59';

register_timeThe field is of datetime type, which needs to be converted to date and then matched. All rows need to be queried for filtering. The second way of writing can be used to create an index on the register_time field, and the query is extremely fast!

Attached date conversion function

DECLARE yt varchar(10); #昨天
DECLARE yt_bt varchar(19); #昨天开始时间
DECLARE yt_et varchar(19); #昨天结束时间
#设置变量  
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');

Related free recommendations: Programming video course

The above is the detailed content of How to optimize mysql query by time. For more information, please follow other related articles on the PHP Chinese website!