ruby on rails - Mysql hosted in Amazon aws is sometimes very slow -

we have database, , tables contains more 2 million records. database hosted in amazon aws server.

sometimes "select" query execution time very slow. reason behind slow execution?

one of query :

select userdid, count(*) exportusers group userdid  having count(*) > 1; 

the query "executing query..." , never ends. forcefully exit query browser.

here explain on select statement

mysql> explain select count(*) exportusers status != 'active' , prenddate < now() - interval 3 month , dtmodified < now() - interval 3 month; +----+-------------+------------+-------+-----------------------------------------------------------------------+--------------------------+---------+------+---------+------------------------------------+ | id | select_type | table      | type  | possible_keys                                                         | key                      | key_len | ref  | rows    |                              | +----+-------------+------------+-------+-----------------------------------------------------------------------+--------------------------+---------+------+---------+------------------------------------+ |  1 | simple      | exportusers | range | index_jobs_on_dtmodified,index_jobs_on_prenddate,index_jobs_on_status | index_jobs_on_dtmodified | 5       | null | 2377287 | using index condition; using | +----+-------------+------------+-------+-----------------------------------------------------------------------+--------------------------+---------+------+---------+------------------------------------+ 1 row in set (0.30 sec) 

there lot of reasons slow query on larger table. few:

• there no index can used speed query.
• the database not have portions of index needed process request loaded memory.
• the requested action competing other queries or updates database processing. in particular, queries , updates don't along well.
• you running database on micro instance offers "bursty" performance , can @ times balk @ "overperforming" given price point.
• you running database on instance using provide web services, cron jobs, software development, email or other processing efforts.

note: of these items may influenced database engine in use.