0

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.

0
3

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
0
1

Start over with the schema. Use a single DATETIME column. Soooo many things will be easier. And more efficient.

If you still have performance problems, start a new question and include EXPLAIN SELECT ... and SHOW CREATE TABLE.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.