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.