List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:March 6 2010 4:43am
Subject:Re: Index not being used
View as plain text  
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
>

Thread
Index not being usedSimon Kimber5 Mar
  • Re: Index not being usedJohnny Withers5 Mar
    • Re: Index not being usedAnanda Kumar6 Mar
      • Re: Index not being usedJoerg Bruehe8 Mar