Mysql hosted on Amazon aws, sometimes very slow

Question

We have a database, and the tables contain more than 2 million records. The database is hosted on Amazon aws.

Sometimes the execution time of a select request is very slow. What could be causing this slow execution?

One of my queries:

select UserDID, count(*) from exportusers
group by UserDID 
having count(*) > 1;

The request says " executing query..." and it never ends. I resolutely exit the query browser.

Here is my selection explanation

mysql> Explain select count(*) from exportusers where status != 'active' and PREndDate < now() - interval 3 month and DTModified < now() - interval 3 month;
+----+-------------+------------+-------+-----------------------------------------------------------------------+--------------------------+---------+------+---------+------------------------------------+
| id | select_type | table      | type  | possible_keys                                                         | key                      | key_len | ref  | rows    | Extra                              |
+----+-------------+------------+-------+-----------------------------------------------------------------------+--------------------------+---------+------+---------+------------------------------------+
|  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 where |
+----+-------------+------------+-------+-----------------------------------------------------------------------+--------------------------+---------+------+---------+------------------------------------+
1 row in set (0.30 sec)

Answer 1

There can be many reasons for a slow query in a larger table. Some:

• There is no index that can be used to speed up the query.
• There are no index parts in the database needed to process a request already loaded into memory.
• . , .
• -, "" "" .
• , -, cron, , .

. .