How to reduce the time taken ...... ?

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I have a table, wherein i am storing all attempts/access for every client. From this table, i would be populating another (report) table. From this report table, i would be populating a graph which shows the attempts/access form a particular client for last 7 days. To make the graph more readable, while populating i am grouping the access time by 4 hour slot i.e,

mysql> select CONCAT(DATE(HOUR)," ",SEC_TO_TIME(CEIL(TIME_TO_SEC(HOUR)/14400)*14400-1)) as ProcessedTIME,HOUR from Attempts_Vs_Client limit 1;
±--------------------±--------------------+
| ProcessedTIME | HOUR |
±--------------------±--------------------+
| 2008-07-01 11:59:59 | 2008-07-01 10:45:00 |
±--------------------±--------------------+
1 row in set (0.00 sec)

In production, this query is taking more time and below is an extract from slow query log file.

Time: 080701 17:27:49

User@Host: root[root] @ localhost [127.0.0.1]

Query_time: 39.437500 Lock_time: 0.000000 Rows_sent: 21 Rows_examined: 40031

SET timestamp=1214913469;
select (MIN(AVCT_ID)-1) as attempt_min, MAX(AVCT_ID) as attempt_max, RID as resid, CONCAT(DATE(HOUR)," ",SEC_TO_TIME(CEIL(TIME_TO_SEC(HOUR)/14400)*14400-1)) as TIME from Attempt_Vs_Client where Attempt_Vs_Client.AVCT_ID <= 5143601 and Attempt_Vs_Client.AVCT_ID >4947183 group by RID,TIME order by NULL;

Additional Details :-

  1. Table type → MyISAM
  2. Indexed Columns :- AVCT_ID(PK), HOUR, RID
  3. MySQL :- 5.1
  4. Total Rows :- ~ 5,00,000.

Below is the output of explain query,

mysql> explain select (MIN(AVCT_ID)-1) as Attempt_min, MAX(AVCT_ID) as Attempt_max, RID as resid, CONCAT(DATE(HOUR)," ",SEC_TO_TIME(CEIL(TIME_TO_SEC(HOUR)/14400)*14400-1)) as TIME from Attempt_Vs_Client where Attempt_Vs_Client.AVCT_ID <= 5143601 and Attempt_Vs_Client.AVCT_ID >4947183 group by RID,TIME order by NULL;
±—±------------±--------------±------±--------------+ ---------±--------±-----±------±------------------------ -----+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±--------------±------±--------------+ ---------±--------±-----±------±------------------------ -----+
| 1 | SIMPLE | Attempt_Vs_Client | range | PRIMARY | PRIMARY | 8 | NULL | 42202 | Using where; Using temporary |
±—±------------±--------------±------±--------------+ ---------±--------±-----±------±------------------------ -----+
1 row in set (0.00 sec)

Appreciate, if you guys can help me to reduce the amount of time consumed by this particular query ?

Thanks in advance,
-S-