MySQL 查询在较长的日期范围内会大大减慢。为什么？

Question

我正在寻求有关如何使以下查询更高效的帮助。目前，它可以工作，但当周范围增加到超过一两周后，效率就不高了。

我现在正在发布查询，但如果您需要更多信息，请告诉我。

SQL Fiddle

解释结果

id select_type        table type  possible_keys key     key_len ref    rows Extra
 1 PRIMARY            u     const PRIMARY       PRIMARY 8       const  1    Using temporary; Using filesort
 1 PRIMARY            A     ALL   (NULL)        (NULL) (NULL)   (NULL) 9    Using where
 1 PRIMARY            B     ALL   (NULL)        (NULL) (NULL)   (NULL) 9    Using where; Using join buffer (flat, BNL join)
 3 DEPENDENT SUBQUERY C     ALL   (NULL)        (NULL) (NULL)   (NULL) 9    Using where

基本上，这是一个时钟应用程序。

用户通过选择项目和任务来打卡。

然后将该记录插入到带有日期/时间戳的 tbl_user_time_log 中。

概述

当用户进入同一项目和不同任务时，会创建另一个带有日期/时间戳的时钟记录。

只要这个“最近打卡时间”不是下类打卡，这个查询现在就能够计算上一个打卡时间与当前打卡时间之间的总时间差。

它将继续以邮票对的形式进行计算，直到到达下类时间，并且如果同一天内有超过一次下类时间。

SELECT USER_ID,
u.USE_TYPE,
u.USE_FIRST_NAME,
u.USE_LAST_NAME,
UTL_ACTIVITY,
UTL_EVENT,
UTL_TASK,
u.USE_PAY_RATE,
SUM(UTL_DURATION) AS UTL_DURATION_TOTAL       
FROM (

SELECT A.PK_USER_TIME_LOG_ID,
A.CLIENT_ID,
A.PROJECT_ID,
A.USER_ID,
A.UTL_DTSTAMP,
/* DATE_FORMAT(A.UTL_DTSTAMP,'%H:%i:%s') AS UTL_DTSTAMP, */
A.UTL_LATITUDE,
A.UTL_LONGITUDE,
A.UTL_ACTIVITY,
A.UTL_EVENT,
A.UTL_TASK,
/* DURATION in seconds  */
TIMESTAMPDIFF(SECOND, A.UTL_DTSTAMP, B.UTL_DTSTAMP) AS UTL_DURATION
FROM   tbl_user_time_log A
/* Alias table (B) begins... */
INNER JOIN tbl_user_time_log B 
/* Inner Join First condition */
ON B.UTL_DTSTAMP = (
/* Inner Join > Inner Select */
/* Magical... */
SELECT 
MIN(UTL_DTSTAMP) 
/* Alias table (C) begins... */
FROM tbl_user_time_log C 
/* Alias table (C) condition(s)... */
WHERE 
C.USER_ID = A.USER_ID 
AND C.UTL_DTSTAMP > A.UTL_DTSTAMP
) 
/* Inner Join Second condition */
AND B.USER_ID = A.USER_ID 
/* Derived table (1) condition(s)... */
WHERE  A.USER_ID = '465617'
/* Between current pay period Start date and Current pay period end date */
/* First day of the week is Monday.*/
-- AND DATE(A.UTL_DTSTAMP) = CURDATE()
AND ( A.UTL_DTSTAMP BETWEEN '2019-07-22 00:00:00' AND '2019-07-29 23:59:59' )
/* Filter out Clock Out. */
AND A.UTL_EVENT <> 'CLOCK OUT'

/* Our derived table... */
) AS tbl_derived_1

INNER JOIN tbl_user u ON u.PK_USER_ID = tbl_derived_1.USER_ID

GROUP BY USER_ID, UTL_TASK
ORDER BY u.USE_FIRST_NAME ASC, UTL_TASK ASC;

Answer 1

目前仅考虑 tbl_衍生_1，我想知道以下 a) 是否返回与派生表相同的结果，而 b) 是否更优化？

   SELECT a.pk_user_time_log_id
        , a.client_id
        , a.project_id
        , a.user_id
        , a.utl_dtstamp
        , a.utl_latitude
        , a.utl_longitude
        , a.utl_activity
        , a.utl_event
        , a.utl_task
        , TIMESTAMPDIFF(SECOND, a.utl_dtstamp, MIN(b.utl_dtstamp)) utl_duration
     FROM tbl_user_time_log a
     JOIN tbl_user_time_log b
       ON b.user_id = a.user_id
      AND b.utl_dtstamp > a.utl_dtstamp
    WHERE a.user_id = 465605
      AND a.utl_dtstamp BETWEEN '2019-08-19 00:00:00' AND '2019-08-25 23:59:59'
      AND a.utl_event <> 'clock out'
    GROUP
       BY a.pk_user_time_log_id
        , a.client_id
        , a.project_id
        , a.user_id
        , a.utl_dtstamp
        , a.utl_latitude
        , a.utl_longitude
        , a.utl_activity
        , a.utl_event
        , a.utl_task;