Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2008
    Posts
    1

    Unanswered: How to reduce the time ?

    Hi,

    One of my query is taking a lot of time to return. Below is the extract from the slow query logs. In this case, the query has taken around 40 seconds to return.

    # 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(LID)-1) as l_min, MAX(LID) as l_max, CONCAT(DATE(HOUR)," ",SEC_TO_TIME(CEIL(TIME_TO_SEC(HOUR)/14400)*14400-1)) as TIME from AllLogins where AllLogins.LID <= 5143601 and AllLogins.LID >4947183 group by TIME order by NULL;

    To populate a graph, which shows the login attempts for last 7 days, i am grouping the all attempts (for a user) in 4 hour slot and plotting the same. Something similar to the below,

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

    Below are the additional details,

    Table Type : - MyISAM.
    Following columns are indexed :- LID (PK), HOUR
    MySQL Version: 5.1
    O.S :- Windows
    Total No: of Rows :- ~ 4,00,000.

    Below is the output of explain query,

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

    I want to reduce the time consumed by this query. Do suggest me some ways.

    bye,
    -3M-.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I don’t have access to MySQL at work and your code is a bit confusing but I think the following is close :
    Code:
    select DATE(HOUR) as TheDate, 
           HOUR(HOUR) / 4 as TimePeriod, 
           count(*) as cnt
    from   AllLogins
    where  HOUR >= dateadd( Day,-7,now() )
    group by DATE( HOUR ), HOUR( HOUR ) / 4
    Mike

    PS Your field names leave a lot to be desired.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •