Speed up query in mysql

Question

How can I speed up this query:

 SELECT
 ( select field_a1
 from tableTen a2 
 where week(a1.date) = week(a2.date)
 and substr(a1.date,1,4) = substr(a2.date,1,4)
 and a2.time >= 0900
 AND a2.time <= 1730 
 order by date asc, time asc limit 1
 ) as fieldValue_a1, 
 MAX(field_a2) as fieldValue_a2,
 MIN(field_a3) as fieldValue_a3, 
 ( select field_a4 
 from tableTen a2
 where week(a1.date) = week(a2.date) 
 and substr(a1.date,1,4) = substr(a2.date,1,4)
 and a2.time >= 0900 
 AND a2.time <= 1730
 order by date desc, time desc limit 1
 ) as fieldValue_a4, 
 SUM(field_a5) as fieldValue_a5, 
 date,
 time 
FROM tableTen a1 
WHERE date >= date_sub(STR_TO_DATE(20160829,'%Y%m%d'), interval 200.0 week) 
 and time >= 0900 AND time <= 1730
 GROUP BY week(date), substr(date,1, 4)
 ORDER BY date DESC

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 | |
| fieldValue_a1 | double | YES | | NULL | |
| fieldValue_a2 | double | YES | | NULL | |
| fieldValue_a3 | double | YES | | NULL | |
| fieldValue_a4 | double | YES | | NULL | |
| fieldValue_a5 | int(11) | 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 | 581 | NULL | NULL | YES | BTREE | | |
 | tableTen | 1 | ten_full_idx | 2 | date | A | 593931 | NULL | NULL | YES | BTREE | | |
 +----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Explain query gives

+----+--------------------+-------+------+--------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+--------------+------+---------+------+--------+----------------------------------------------+
| 1 | PRIMARY | a1 | ALL | ten_full_idx | NULL | NULL | NULL | 594441 | Using where; Using temporary; Using filesort |
| 3 | DEPENDENT SUBQUERY | a2 | ALL | ten_full_idx | NULL | NULL | NULL | 594441 | Using where; Using filesort |
| 2 | DEPENDENT SUBQUERY | a2 | ALL | ten_full_idx | NULL | NULL | NULL | 594441 | Using where; Using filesort |
+----+--------------------+-------+------+--------------+------+---------+------+--------+----------------------------------------------+

Table status

+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| tableTen | MyISAM | 10 | Dynamic | 594441 | 62 | 37389700 | 281474976710655 | 5325824 | 0 | NULL | 2016年08月29日 19:41:47 | 2016年08月30日 17:49:24 | 2016年08月29日 19:41:49 | latin1_swedish_ci | NULL | | |
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+

The field date contains date in format aaaammdd ex.: 20141027

The field time contains time in format hhmm ex.: 0809

A select requires about 4 minutes in a table with about 600000 rows.

How can I speed up the query ?

Update:

After the advice given to me by @dnoeth and @Andriy M I have inserted new field, my_date and my_time with correct data types, DATE and TIME using these queries:

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');

then I've created a new index, ten_idx:

+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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 | 595482 | NULL | NULL | YES | BTREE | | |
| tableTen | 1 | ten_idx | 1 | my_date | A | 1158 | NULL | NULL | YES | BTREE | | |
| tableTen | 1 | ten_idx | 2 | my_time | A | 595482 | NULL | NULL | YES | BTREE | | |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

After the advice given to me by @dnoeth please refer your answers to the following query and not to the previous:

SELECT 
 a2.field_a1,
 fieldValue_a2,
 fieldValue_a3,
 a3.field_a4,
 fieldValue_a5,
 maxDate,
 maxTime
FROM
 (
 SELECT
 -- min/max values for following joins (if they're not unique you might add another GROUP BY in the outer Select
 -- this will add the time as "minutes & seconds" instead of "hours & minutes", but the next step will extract it correctly 
 DATE(MIN(TIMESTAMP(my_date,my_time))) as minDate,
 TIME(MIN(TIMESTAMP(my_date,my_time))) as minTime,
 DATE(MAX(TIMESTAMP(my_date,my_time))) as maxDate,
 TIME(MAX(TIMESTAMP(my_date,my_time))) as maxTime,
 MAX(field_a2) as fieldValue_a2,
 MIN(field_a3) as fieldValue_a3, 
 SUM(field_a5) as fieldValue_a5
 FROM tableTen 
 WHERE my_date >= date_sub(DATE '2016-08-29', interval 200.0 week)
 and my_time BETWEEN TIME '09:00:00' AND TIME '17:30:00'
 GROUP BY week(my_date), year(my_date)
 ) as a1
left join tableTen a2 -- minimum timestamp within week
 on a1.minDate = a2.my_date
 and a1.minTime = a2.my_time
left join tableTen a3 -- maximum timestamp within week
 on a1.maxDate = a3.my_date
 and a1.maxTime = a3.my_time
ORDER BY maxDate DESC

Once the work is completed I will remove the fields date and time of type char and its index.

• Rick James: This question seems to be continued in dba.stackexchange.com/questions/148779/…

Answer 1

This should return the expected result:

SELECT 
 a2.field_a1,
 fieldValue_a2,
 fieldValue_a3,
 a3.field_a4,
 fieldValue_a5,
 maxDate,
 maxTime
FROM
 (
 SELECT
 -- min/max values for following joins (if they're not unique you might add another GROUP BY in the outer Select
 -- this will add the time as "minutes & seconds" instead of "hours & minutes", but the next step will extract it correctly 
 DATE(MIN(TIMESTAMP(date,time))) as minDate,
 TIME(MIN(TIMESTAMP(date,time))) as minTime,
 DATE(MAX(TIMESTAMP(date,time))) as maxDate,
 TIME(MAX(TIMESTAMP(date,time))) as maxTime,
 MAX(field_a2) as fieldValue_a2,
 MIN(field_a3) as fieldValue_a3, 
 SUM(field_a5) as fieldValue_a5
 FROM tableTen 
 WHERE -- date is a CHAR, so better compare to a CHAR instead of DATE
 date >= DATE_FORMAT(date_sub(STR_TO_DATE(20160829,'%Y%m%d'), interval 200.0 week), '%Y%m%d')
 -- time is a CHAR, so better compare to a CHAR instead of a numeric value 
 and time >= '0900' AND time <= '1730'
 GROUP BY week(date), substr(date,1, 4)
 ) as a1
left join tableTen a2 -- minimum timestamp within week
 on DATE_FORMAT(minDate,'%Y%m%d') = a2.date
 -- extract "minutes & seconds" as "hours & minutes"
 and DATE_FORMAT(minTime,'%i%s') = a2.time
left join tableTen a3 -- maximum timestamp within week
 on DATE_FORMAT(maxDate,'%Y%m%d') = a3.date
 and DATE_FORMAT(maxTime,'%i%s') = a3.time
ORDER BY maxDate DESC

Of course, you should better switch to correct datatypes for your date and time column, either DATE & TIME or a single TIMESTAMP.

After changing the data types it should be as simple as this:

SELECT 
 a2.field_a1,
 fieldValue_a2,
 fieldValue_a3,
 a3.field_a4,
 fieldValue_a5,
 maxDate,
 maxTime
FROM
 (
 SELECT
 -- min/max values for following joins (if they're not unique you might add another GROUP BY in the outer Select
 -- this will add the time as "minutes & seconds" instead of "hours & minutes", but the next step will extract it correctly 
 DATE(MIN(TIMESTAMP(my_date,my_time))) as minDate,
 TIME(MIN(TIMESTAMP(my_date,my_time))) as minTime,
 DATE(MAX(TIMESTAMP(my_date,my_time))) as maxDate,
 TIME(MAX(TIMESTAMP(my_date,my_time))) as maxTime,
 MAX(field_a2) as fieldValue_a2,
 MIN(field_a3) as fieldValue_a3, 
 SUM(field_a5) as fieldValue_a5
 FROM tableTen 
 WHERE my_date >= date_sub(DATE '2016-08-29', interval 200.0 week)
 and my_time BETWEEN TIME '09:00:00' AND TIME '17:30:00'
 GROUP BY week(my_date), year(my_date)
 ) as a1
left join tableTen a2 -- minimum timestamp within week
 on a1.minDate = a2.my_date
 and a1.minTime = a2.my_time
left join tableTen a3 -- maximum timestamp within week
 on a1.maxDate = a3.my_date
 and a1.maxTime = a3.my_time
ORDER BY maxDate DESC

• Paul White: Comments are not for extended discussion; this conversation has been moved to chat.