Re: select query not using indexes

[senthil kumaran]

Hi,
Thanks Alon and Rick James.

I'm using MYISAM engine. ID used for where condition is the PK of the table.

Show create table output is

`UBT_ID` bigint(19) NOT NULL default '0',
`HOUR` datetime NOT NULL default '0000-00-00 00:00:00',
`RID` bigint(19) default NULL,
`SC_ID` bigint(19) NOT NULL default '0',
`SC` char(96) NOT NULL default '',
`USER_ID` bigint(15) default NULL,
`USER` char(90) default NULL,
`REQUEST_COUNT` int(15) default '0',
`DURATION` int(15) default '0',
`B_IN` bigint(15) default '0',
`B_OUT` bigint(15) default '0',
`TOTAL` bigint(15) default '0',
`BTYPE` int(5) default '1',
PRIMARY KEY (`UBT_ID`),
KEY `Book_CIDX1` (`HOUR`),
KEY `Book_CIDX2` (`SC_ID`),
KEY `Book_CIDX3` (`RID`),
KEY `Book_CIDX4` (`USER_ID`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1 |


My query is
select UBT_ID,CONCAT(DATE(HOUR)," ",SEC_TO_TIME(CEIL(TIME_TO_SEC(HOUR)/14400)*14400-1)) as HOUR,RID,SC_ID,SC,USER_ID,USER,sum(REQUEST_COUNT) as REQUEST_COUNT, sum(DURATION) as DURATION, sum(B_IN) as B_IN, sum(B_OUT) as B_OUT, sum(TOTAL) as TOTAL_BOOKS, BTYPE from Book where ( (Book.UBT_ID<= $#usr_max# and Book.UBT_ID>$#usr_min#) and Book.BTYPE=0 ) group by Book.RID,HOUR,Book.SC_ID,Book.USER_ID ORDER BY NULL

explain result with less range values
+----+-------------+----------------------+-------+---------------+---------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+-------+---------------+---------+---------+------+------+------------------------------+
| 1 | SIMPLE | Book | range | PRIMARY | PRIMARY | 8 | NULL | 888 | Using where; Using temporary |
+----+-------------+----------------------+-------+---------------+---------+---------+------+------+------------------------------+

explain result with some huge range values

+----+-------------+-------+------+---------------+------+---------+------+--------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+------------------------------+
| 1 | SIMPLE | Book | ALL | PRIMARY | NULL | NULL | NULL | #### | Using where; Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+--------+------------------------------+


Please suggest how to improve my query.

Thanks in Advance
RSK