MySQL: speed query that uses the time intervals

Question

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.

Answer 1

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

Answer 2

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.