Hi Everyone, I have the following table: CREATE TABLE `form_fields_items` ( `ID` int(11) NOT NULL auto_increment, `siteid` int(11) NOT NULL default '0', `fieldid` int(11) NOT NULL default '0', `value` varchar(150) NOT NULL default '', `sortorder` int(11) NOT NULL default '0', PRIMARY KEY (`ID`), KEY `siteid` (`siteid`), KEY `fieldid` (`fieldid`), KEY `sortorder` (`sortorder`), KEY `sitefieldsort` (`siteid`,`fieldid`,`sortorder`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=61219 ; And I am running the following query: SELECT * FROM form_fields_items WHERE siteid = 1234 AND fieldid = 5678 ORDER BY sortorder
And an explain returns the following: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE form_fields_items ref siteid,fieldid,sitefieldsort fieldid 4 const 9 Using where; Using filesort Can anyone tell me why this is not using the sitefieldsort index? If I change the query to something that returns no rows, such as: SELECT * FROM form_fields_items WHERE siteid = 1 AND fieldid = 1 ORDER BY sortorder An explain shows it using the correct index. Thanks for your time! Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
