Index not being used

Hi Everyone,
=20
I have the following table:
=20
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=3DMyISAM DEFAULT CHARSET=3Dlatin1 AUTO_INCREMENT=3D61219 ;
=20
And I am running the following query:
=20
SELECT * FROM form_fields_items WHERE siteid =3D 1234 AND fieldid =3D =
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 =3D 1 AND fieldid =3D 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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Index not being used

--001636832ef2e950eb048111cac9
Content-Type: text/plain; charset=ISO-8859-1

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 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=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--001636832ef2e950eb048111cac9--

Re: Index not being used

--001636ed62f96c6b2904811a7885
Content-Type: text/plain; charset=ISO-8859-1

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 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 > >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=johnny@pixelated.net
> >
> >
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@pixelated.net
>

--001636ed62f96c6b2904811a7885--

Re: Index not being used

Hi all!


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

NO! NEVER!
(sorry for shouting)

For any DBMS (this is not specific to MySQL), it is not possible to u=
se
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 in=
dex
usable for the query mentioned.

More inserts below.

>=20
> On Fri, Mar 5, 2010 at 11:52 PM, Johnny Withers et>wrote:
>=20
>> [[...]]
>>
>> On Fri, Mar 5, 2010 at 10:42 AM, Simon Kimber ..ltd.uk
>>> 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=3DMyISAM DEFAULT CHARSET=3Dlatin1 AUTO_INCREMENT=3D61219=
;
>>>
>>> And I am running the following query:
>>>
>>> SELECT * FROM form_fields_items WHERE siteid =3D 1234 AND fieldid=
=3D 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 U=
sing
>>> 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 migh=
t
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 =3D 1 AND fieldid =
=3D 1 ORDER
>>> BY sortorder
>>>
>>> An explain shows it using the correct index.

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

If the query predicate matches a high percentage of the base data, th=
en
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 (effective=
ly)
random order.


Jörg

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg