MySql MyISAM indexes question

[jkon]

Hello all,
I have a table dir_records < SHOW CREATE TABLE dir_records >
CREATE TABLE `dir_records` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`TITLE` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`URL_TITLE` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`TXT` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`COMPANY_ID` int(11) NOT NULL,
`CATEGORY_ID` int(11) NOT NULL,
`PRIORITY` int(5) NOT NULL DEFAULT '0',
`STATUS` int(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`ID`),
UNIQUE KEY `COMPANY_URL_INDEX` (`COMPANY_ID`,`URL_TITLE`),
UNIQUE KEY `CATEGORY_URL_INDEX` (`CATEGORY_ID`,`URL_TITLE`),
KEY `CATEGORY_STATUS_INDEX` (`CATEGORY_ID`,`STATUS`),
KEY `PRIORITY` (`PRIORITY`),
KEY `COMPANY_ID` (`COMPANY_ID`)
) ENGINE=MyISAM

The index CATEGORY_STATUS_INDEX I created for the following query
SELECT ID,TITLE,URL_TITLE,COMPANY_ID FROM dir_records WHERE CATEGORY_ID = ? AND STATUS = 1 ORDER BY PRIORITY ASC

But when I execute < EXPLAIN SELECT ID,TITLE,URL_TITLE,COMPANY_ID FROM dir_records WHERE CATEGORY_ID = 4 AND STATUS = 1 ORDER BY PRIORITY ASC >
I get:
possible_keys : CATEGORY_URL_INDEX,CATEGORY_STATUS_INDEX
key: CATEGORY_URL_INDEX

Does this mean that it dint did any good adding the CATEGORY_STATUS_INDEX and if so why it prefers CATEGORY_URL_INDEX over CATEGORY_STATUS_INDEX ?
Any ideas would be valuable.

[smantscheff]

As far as I understood it the query optimization uses only one of the indexed fields in the query execution. For some reasons it concludes that this field is CATEGORY_ID and therefore chooses an index which contains this field. The second field in your index is not taken into consideration by the optimizer. So for this query you - to the best of my knowledge - might drop the second index.