Re: select query not using indexes
First, to shrink the table size (which helps in disk I/O, hence query performance):
`BTYPE` int(5) is using 32 bits so store small numbers? Consider using SMALLINT.
`REQUEST_COUNT` int(15) will not let you store 999,999,999,999,999 -- it will top out at about 2,000,000,000. Perhaps you meant BIGINT? Or did not mean (15).
Also consider UNSIGNED.
`SC` char(96) -- are they always 96 characters long? Consider using VARCHAR(96).
etc.
"ORDER BY NULL" adds nothing.
How often is BTYPE=0? If it is infrequent, then this will cut down on the number of rows to ponder:
INDEX (BTYPE, UBT_ID)
or
INDEX (BTYPE, UBT_ID, RID, HOUR, SC_ID, USER_ID)
Even if BTYPE is usually 0, either of the above may help.
To explain...
INDEX (BTYPE, UBT_ID)
would let it get the desired rows in one range scan within the index, but still have to go to the data for the other processing.
INDEX (BTYPE, UBT_ID, RID, HOUR, SC_ID, USER_ID)
would let it do that, plus be ready to do the GROUP BY in exactly the desired order. This should save some effort.
Why 2 diff EXPLAINs? When it has to read "a lot" of the table, it decides to skip the indexes and head straight for the table. Why? Going thru any index in MyISAM first involves reading some "rows" in the index (these are usually consecutive). As it does that, it has to reach over into the data via a random read for each row.
My suggestions of indexes may, for the same reason, fail to prevent simply doing a table scan.
If, on the other hand, this were InnoDB, and the PK were on (BTYPE, UBT_ID), the range scan would be in the data, without the random read. (Note: your business logic may also need UNIQUE(UBT_ID).)
Will switching to InnoDB make it faster? Maybe. It would go something like:
ALTER TABLE Book
MODIFY COLUMN `SC` varchar(96) NOT NULL default '',
(etc)
DROP PRIMARY KEY,
ADD PRIMARY KEY (BTYPE, UBT_ID),
ADD UNIQUE (UBT_ID),
ENGINE=InnoDB;
(Plz backup table first, and check syntax.)
Doing all the alters at once is N times faster than doing them one at a time.