List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:March 5 2010 6:22pm
Subject:Re: Index not being used
View as plain text  
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