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 <johnny@stripped>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 <simon@stripped
> >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=1
> >
> >
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@stripped
>
