AWS RDS MySql Simple query with composite index and date range took too long to execute out of ~8 millions data

Question

Query is really simple i.e

SELECT 
    col1 , date_col 
FROM table USE INDEX (device_date_col) 
WHERE 
    device_id = "some_value"
    AND date_col BETWEEN "2020-03-16 00:00:00" and "2020-04-16 00:00:00" 
limit 1000000 ;

but it takes 30 to 60 seconds to finally returns the result, when running first time. And then it returns result under 10 seconds. And another problem is that when I change the device_id it again takes long time. I cannot understand why it's happening beside using proper indexing.

We know that, API Gateway has 30 seconds limit due to this our API encounter timeout. It happens suddenly from today.

Main goal is to retrieve minutely data, it returns less data but also takes long time i.e

 ....
 AND col1 IS NOT NULL
 GROUP BY 
    DATE(date_col),
    HOUR(date_col), 
    MINUTE(date_col)

Below are some useful info

1. AWS RDS having instance db.m4.large (vCPU 2 and RAM 8GB).
2. MySql version 5.6.x
3. composite index on date_col and device_col
4. using InnoDB
5. table has no id field (primary key)
6. total rows in table are 7.5 million
7. each device has data every 3 seconds
8. query return rows around 600k
9. explain query shows it is using indexing
10. 

UPDATE

MySql Workbench shows that when I run query without group by it takes 2 seconds to execute but > 30 seconds to retrieve and when I use group by then server takes > 30 to execute but 2 seconds to retrieve. I think we need to more

• CPU for processed data using group by
• More RAM for extracting all data (without group by)

Below Image is showing the query response without group by. Look at the duration/Fetch time

Answer 1

(original query)

SELECT  col1 , date_col
    FROM  table USE INDEX (device_date_col)
    WHERE  device_id = "some_value"
      AND  date_col BETWEEN "2020-03-16 00:00:00"
                        AND "2020-04-16 00:00:00"
    limit  1000000 ;

Discussion of INDEX(device_id, date_col, col1)

1. Start an index with = column(s), name,y device_id. This focuses the search somewhat.
2. Within that, further focus on the date range. So, add date_col to the index. You now have the optimal index for the WHERE
3. Tack on all the other columns showing up anywhere in the SELECT if it is not too many columns and includes no TEXT columns. Now you have a "covering" index. This allows the query to be performed using just the index's BTree, thereby giving a further boost in speed.

More discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Other notes

• LIMIT without ORDER BY is usually not meaningful -- you are at risk of getting a random set of rows.

• That BETWEEN includes an extra midnight. I suggest

  AND  date_col >= "2020-03-16"
  AND  date_col  < "2020-03-16" + INTERVAL 1 MONTH
  

• Remove the USE INDEX -- It may help today, but it could hurt tomorrow, when the data changes or the constants change.

• LIMIT 1000000 -- This could choke some clients. Do you really need that many rows? Perhaps more processing could be done in the database?
• Adding on the GROUP BY -- Could there be two values for col1 within some of the minutes? Which value of col1 will you get? Consider MAX(col1), ANY_VALUE(col1), or GROUP_CONCAT(DISTINCT col1).