Mysql – Speed up query in thesql

MySQLperformancequery-performance

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.

Best Answer

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

Best Answer

Related Solutions

Mysql – EXPLAIN output suggests that the index is not being used

MySQL optimization – year column grouping – using temporary table, filesort

Related Question