CODEWITHSUNDEEP
mysqldatabaseamazon-web-servicesamazon-rdsquery-performance
Hani Elmalky
Hani Elmalky

Reputation: 21

The RDS instance CPU is 99%

Query taking a long time to execute in AWS RDS

The one query is taking too much time around 3 minutes. Db is hosted in AWS RDS with 2cpu and 4GB ram (t2. medium)

The Query Sample is

select  DATE_FORMAT(log_date, '%d-%m-%Y') as 'Day',
        sum(people_count) as 'count'"
    from  avg_people_pass
    where  log_date >= #startDate
      and  log_date <= #endDate

appreciate your help because it lagging all the solution.

Upvotes: 1

Views: 892

Answers (2)

Gordan Bobić
Gordan Bobić

Reputation: 1858

This should make it run as fast as it's ever going to, assuming you don't already have an index on this column:

CREATE INDEX idx1 ON avg_people_pass (log_date);

Upvotes: 1

mcfinnigan
mcfinnigan

Reputation: 11638

select DATE_FORMAT(log_date, '%d-%m-%Y') as 'Day',sum(people_count) as 'count' from avg_people_pass where log_date >= #startDate and log_date <= #endDate

I'm guessing you have no indexes configured on this table and that as a result you're doing a full table scan. 3 minute execution time implies a woefully designed, large table with no indexing, keys or optimization.

What happens when you run

explain select DATE_FORMAT(log_date, '%d-%m-%Y') as 'Day',sum(people_count) as 'count' from avg_people_pass where log_date >= #startDate and log_date <= #endDate

A query plan will help narrow down the performance issues.

Upvotes: 2

Related Questions