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

I don’t have access to MySQL at work and your code is a bit confusing but I think the following is close :
Mike

PS Your field names leave a lot to be desired.