I don't have your data, so I do not really know if my results are "correct".
I created this table on my system and added two records: INSERT INTO form_fields_items(siteid,fieldid,`value`,sortorder) VALUES (1234,5678,'test1',0) ; INSERT INTO form_fields_items(siteid,fieldid,`value`,sortorder) VALUES (4321,8765,'test2',0) ; Both of your sample selects use the sitefieldsort key with a key_len of 8. The extra part of explain's output reads "Using where". MySQL Server version 5.0.77-log. Maybe you need to run ANALYZE TABLE on this table? JW On Fri, Mar 5, 2010 at 10:42 AM, Simon Kimber <si...@internetstuff.ltd.uk>wrote: > 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=joh...@pixelated.net > > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net
