2

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.

Improve this question
1

1 Answer 1

Reset to default
2

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
Improve this answer
1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White ♦
    Commented Sep 2, 2016 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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