List:General Discussion« Previous MessageNext Message »
From:Simon Kimber Date:March 5 2010 4:42pm
Subject:Index not being used
View as plain text  
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
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