CODEWITHSUNDEEP
mysqlrds
sanath meti
sanath meti

Reputation: 6539

Query taking long time to execute in AWS RDS

I am working on some temp tables for practice. The one query is taking too much of time around 550 sec.Db is hosted in AWS RDS with 8cpu and 16GB ram. Below query has to be run in different DB( prod ) , first checking in test testDB

create table test_01 as  
 select * 
    from
        (
   select
    person
                ,age
                ,dob
                ,place
             from
     person
   where
     person is not null
                and age is not null
                and dob is not null
                and place is not null
    limit 1000
  ) ps_u
 left join
  employee em_u
  on ps_u.age = em_u.em_age
  and ps_u.place = em_u.location
 order by person
    limit 1000

Is there issue with query or with the resource, CPU utilization shows 30% ram is ok not too much. Let me know any suggestion to optimize the query.

Upvotes: 1

Views: 2934

Answers (4)

user4936563
user4936563

Reputation: 81

In case you are limiting the result (with limit 1000) - do you really need order by person? If the result is huge - order by could adversely affect the performance.

Upvotes: 1

sanath meti
sanath meti

Reputation: 6539

I solved it by creating index for the column

alter table person
add fulltext index `fulltext`

    (
     , person asc
        , age asc
        , dob asc
        , place asc
    )
    ;

And then the query took only 3 seconds for 1000 records

Upvotes: 0

Null Pointer
Null Pointer

Reputation: 466

You can reduce 1 select statement / also left join bring all records from left table could take time to process data.

CREATE TABLE test_01 AS
  (SELECT person,
          age,
          dob,
          place
   FROM person ps_u
   LEFT JOIN employee em_u ON ps_u.age = em_u.em_age
   AND ps_u.place = em_u.location
   ORDER BY ps_u.person
   WHERE ps_u.person IS NOT NULL
     AND ps_u.age IS NOT NULL
     AND ps_u.dob IS NOT NULL
     AND ps_u.place IS NOT NULL
   LIMIT 1000)

Upvotes: 0

Jobayer
Jobayer

Reputation: 1231

  1. check your left join. it can be a reason for it. left join will return everything from your left table, if this table has lot of entry, it will slow down your query.

  2. With it, you can break your query in two separate query & check execution time using different tweaking.

  3. Try to return specific rows rather than *.

Upvotes: 1

Related Questions