MySQL – Speed Up Query Using Time Intervals

MySQLperformancequery-performance

How can I speed up this query that uses the time intervals:

SELECT  a2.field_a1,
        MAX(a1.field_a2) AS field_a2,
        MIN(a1.field_a3) as field_a3,
        a3.field_a4,
        SUM(a1.field_a5) as field_a5,
        a1.date,
        MIN(a1.time) as time
    FROM  tableTen a1
    INNER JOIN  
    (
        SELECT  floor( (cast( SUBSTRING(time,1,2) AS SIGNED ) * 60 +
                        cast( SUBSTRING(time,3,2) AS SIGNED ) - 540) /5
                     ) AS timeInterval,
                MIN(time) AS minTime,
                MAX(time) AS maxtime,
                date as timeIntervDate
            FROM  tableTen
            WHERE  TIME >= 0900
              AND  time <= 1730
            GROUP BY  timeIntervDate, timeInterval 
    ) Sub1  ON Sub1.timeIntervDate = a1.date
           AND  floor( (cast( SUBSTRING(a1.time,1,2) AS SIGNED ) * 60 +
                        cast( SUBSTRING(a1.time,3,2) AS SIGNED ) - 540) /5 
                     ) = Sub1.timeInterval
    INNER JOIN  tableTen a2  ON a2.time = Sub1.minTime  AND  a1.date = a2.date
    INNER JOIN  tableTen a3  ON a3.time = Sub1.maxtime  AND  a1.date = a3.date
    WHERE  a1.time >= 0900
      AND  a1.time <= 1730
    GROUP BY  a1.date , timeInterval
    order by  a1.date desc, time desc

an example of the values that contains the table is the following:

+------+--------+----------+------+-----------+-----------+------------+----------+----------+------------+----------+
| id   | symbol | date     | time | field_a1  | field_a2  | field_a3   | field_a4 | field_a5 | my_date    | my_time  |
+------+--------+----------+------+-----------+-----------+------------+----------+----------+------------+----------+
|    0 | TEN    | 20120202 | 0900 | 15.06     | 15.08     | 14.98      |    15    |  31022   | 2012-02-02 | 09:00:00 |
|    1 | TEN    | 20120202 | 0901 | 15.04     | 15.08     | 15.04      | 15.08    |   6834   | 2012-02-02 | 09:01:00 |
|    2 | TEN    | 20120202 | 0902 | 15.06     | 15.07     | 15.06      | 15.07    |   1458   | 2012-02-02 | 09:02:00 |
+------+--------+----------+------+-----------+-----------+------------+----------+----------+------------+----------+

in order to speed up the query I have included my_date and my_time fields (see this link 1)
using the steps below:

ALTER TABLE tableTen ADD my_date date; 
ALTER TABLE tableTen ADD my_time time; 
UPDATE tableTen SET my_date = STR_TO_DATE(date,'%Y%m%d'); 
UPDATE tableTen SET my_time = STR_TO_DATE(time, '%H%i');
CREATE INDEX ten_idx ON tableTen(my_date, my_time) USING BTREE;

now tableTen is:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | YES  |     | NULL    |       |
| symbol    | varchar(25) | YES  |     | NULL    |       |
| date      | char(8)     | YES  |     | NULL    |       |
| time      | char(4)     | YES  | MUL | NULL    |       |
| field_a1  | double      | YES  |     | NULL    |       |
| field_a2  | double      | YES  |     | NULL    |       |
| field_a3  | double      | YES  |     | NULL    |       |
| field_a4  | double      | YES  |     | NULL    |       |
| field_a5  | int(11)     | YES  |     | NULL    |       |
| my_date   | date        | YES  | MUL | NULL    |       |
| my_time   | time        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

the index are

+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tableTen |          1 | ten_full_idx |            1 | time        | A         |         582 |     NULL | NULL   | YES  | BTREE      |         |               |
| tableTen |          1 | ten_full_idx |            2 | date        | A         |      596004 |     NULL | NULL   | YES  | BTREE      |         |               |
| tableTen |          1 | ten_idx      |            1 | my_date     | A         |        1159 |     NULL | NULL   | YES  | BTREE      |         |               |
| tableTen |          1 | ten_idx      |            2 | my_time     | A         |      596004 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

How can I adjust the code

floor( (cast( SUBSTRING(time,1,2) AS SIGNED ) * 60 +
                    cast( SUBSTRING(time,3,2) AS SIGNED ) - 540) /5)

in such a way that it use the field my_time instead of the field time ?

After completing the work, the fields date, time and their index which will no longer be needed will be removed.

Best Answer

There's no need for joining a1 and sub1, this is a variation of your previous question, just adjust it to the 5 minute interval instead of weeks, probably:

SELECT 
   a2.field_a1,
   fieldValue_a2,
   fieldValue_a3,
   a3.field_a4,
   fieldValue_a5,
   maxDate,
   maxTime
FROM
 (
   SELECT
      my_date,
      floor(timestampdiff(minute,time '09:00:00', my_time) /5) AS timeInterval,
      MIN(my_time) as minTime,
      MAX(my_time) as maxTime,
      MIN(my_date) as minDate,
      MAX(my_date) as maxDate,

      MAX(field_a2) as fieldValue_a2,
      MIN(field_a3) as fieldValue_a3, 
      SUM(field_a5) as fieldValue_a5
   FROM tableTen 
   WHERE my_time BETWEEN TIME '09:00:00' AND TIME '17:30:00'
   GROUP BY my_date, timeInterval
 ) as a1
left join tableTen a2
  on a1.my_date = a2.my_date
 and a1.minTime = a2.my_time

left join tableTen a3
  on a1.my_date = a3.my_date
 and a1.maxTime = a3.my_time

ORDER BY minDate DESC, minTime DESC

Related Solutions

Sql-server – query optimization: time intervals

I can't answer your question as to the absolutely best way. But I can offer a different way of solving the problem, which may or may not be better. It has a reasonably flat execution plan, and I think it will perform well. (I'm eager to know so share the results!)

I apologize for using my own syntax style instead of yours--it helps query wizardry come to me when everything lines up in its usual place.

The query is available in a SqlFiddle. I threw in an overlap for EmpID 1 just to be sure I had that covered. If you eventually find that overlaps cannot occur in presence data, then you can remove the final query and the Dense_Rank calculations.

WITH Points AS (
  SELECT DISTINCT
    T.EmpID,
    P.TimePoint
  FROM
    (
      SELECT * FROM dbo.WorkTime
      UNION SELECT * FROM dbo.BreakTime
      UNION SELECT * FROM dbo.Absence
    ) T
    CROSS APPLY (VALUES (StartTime), (EndTime)) P (TimePoint)
), Groups AS (
  SELECT
    P.EmpID,
    P.TimePoint,
    Grp =
      Row_Number()
      OVER (PARTITION BY P.EmpID ORDER BY P.TimePoint, X.Which) / 2
  FROM
    Points P
    CROSS JOIN (VALUES (1), (2)) X (Which)
), Ranges AS (
  SELECT
    G.EmpID,
    StartTime = Min(G.TimePoint),
    EndTime = Max(G.TimePoint)
  FROM Groups G
  GROUP BY
    G.EmpID,
    G.Grp
  HAVING Count(*) = 2
), Presences AS (
  SELECT
    R.*,
    P.Present,
    Grp =
       Dense_Rank() OVER (PARTITION BY R.EmpID ORDER BY R.StartTime)
       - Dense_Rank() OVER (PARTITION BY R.EmpID, P.Present ORDER BY R.StartTime)
  FROM
    Ranges R
    CROSS APPLY (
      SELECT
        CASE WHEN EXISTS (
          SELECT *
          FROM dbo.WorkTime W
          WHERE
            R.EmpID = W.EmpID
            AND R.StartTime < W.EndTime
            AND W.StartTime < R.EndTime
        ) AND NOT EXISTS (
          SELECT *
          FROM dbo.BreakTime B
          WHERE
            R.EmpID = B.EmpID
            AND R.StartTime < B.EndTime
            AND B.StartTime < R.EndTime
        ) AND NOT EXISTS (
          SELECT *
          FROM dbo.Absence A
          WHERE
            R.EmpID = A.EmpID
            AND R.StartTime < A.EndTime
            AND A.StartTime < R.EndTime
        ) THEN 1 ELSE 0 END
    ) P (Present)
)
SELECT
  EmpID,
  StartTime = Min(StartTime),
  EndTime = Max(EndTime)
FROM Presences
WHERE Present = 1
GROUP BY
  EmpID,
  Grp
ORDER BY
  EmpID,
  StartTime;

Note: performance of this query would be improved you combined the three tables and added a column to indicate what kind of time it was: work, break, or absence.

And why all the CTEs, you ask? Because each one is forced by what I need to do to the data. There's an aggregate, or I need to put a WHERE condition on a windowing function or use it in a clause where windowing functions aren't allowed.

Now I'm going to go off and see if I can't think up another strategy for accomplishing this. :)

For amusement I include here a "diagram" I made to help solve the problem:

------------
   -----------------
                ---------------
                           -----------

    ---    ------   ------       ------------

----   ----      ---      -------

The three sets of dashes (separated by spaces) represent, in order: presence data, absence data, and the desired result.

Mysql – Speed up a MySQL BETWEEN query

In your query, the join has no conditions. Shouldnt be something like SELECT xxx FROM table1 JOIN table2 ON table1.column = table2.column and xxx.date between xxx to xxx .

Add: Here is the explain for the test table

explain select find from table1 t1 join table2 t2 on t1.find between t2.from1 and t2.to1;
+----+-------------+-------+-------+-------------------+---------+---------+------+------+------------------------------------------------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref  | rows | Extra                                          |
+----+-------------+-------+-------+-------------------+---------+---------+------+------+------------------------------------------------+
|  1 | SIMPLE      | t2    | index | from1,to1,from_to | from_to | 12      | NULL |    2 | Using index                                    |
|  1 | SIMPLE      | t1    | ALL   | idx               | NULL    | NULL    | NULL |    5 | Range checked for each record (index map: 0x1) |

Best Answer

Related Solutions

Sql-server – query optimization: time intervals

Mysql – Speed up a MySQL BETWEEN query

Related Question