you should change the column order for the index sitefieldsort. It should be (sortorder,siteid`,`fieldid`).
regards anandkl On Fri, Mar 5, 2010 at 11:52 PM, Johnny Withers <joh...@pixelated.net>wrote: > 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 >
