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 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 <joh...@pixelated.net>wrote: > >> [[...]] >> >> On Fri, Mar 5, 2010 at 10:42 AM, Simon Kimber <si...@internetstuff.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=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? 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. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
