GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

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

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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-
Working...
X