List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:March 8 2010 10:07am
Subject:Re: Index not being used
View as plain text  
Hi all!

Ananda Kumar wrote:
> you should change the column order for the index sitefieldsort.
> It should be (sortorder,siteid`,`fieldid`).

(sorry for shouting)

For any DBMS (this is not specific to MySQL), it is not possible to use
a multi-column index unless values are given for the leading columns.
As the query does not give a value for "sortorder", this query could
never use any index whose leading column is "sortorder".

The original order of columns in the index was correct to make the index
usable for the query mentioned.

More inserts below.

> On Fri, Mar 5, 2010 at 11:52 PM, Johnny Withers <johnny@stripped>wrote:
>> [[...]]
>> 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`)
>>> 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?

Sorry, I don't know.
But please provide us with the number of rows in the table, this might
influence the optimizer's choice.

>>> 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.

It would not return any rows, because you have none matching the values
in the query? This makes me suspect it really depends on selectivity:

If the query predicate matches a high percentage of the base data, then
it is more efficient to sequentially scan the base data directly than to
find index entries and use them to access the base data in (effectively)
random order.


Joerg Bruehe,  MySQL Build Team,  Joerg.Bruehe@stripped
Sun Microsystems GmbH,   Komturstraße 18a,   D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels
Vorsitzender des Aufsichtsrates: Martin Haering     Muenchen: HRB161028

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