MySQL Forums :: Optimizer & Parser :: Query that returns indexed fields involves FILESORT, why?


Advanced Search

Query that returns indexed fields involves FILESORT, why?
Posted by: Yurij Zagrebnoy ()
Date: February 26, 2010 12:36PM

They query is pretty simple:

SELECT * FROM objects ORDER BY ObjectName

The table is heavily indexed because many types of queries are done on it.

EXPLAIN returns this:

>EXPLAIN SELECT ObjectTypeID FROM objects ORDER BY ObjectName;
id select_type table   type possible_keys key  key_len ref  rows   Extra
1  SIMPLE      objects ALL  NULL          NULL NULL    NULL 122077 Using filesort

If I use only primary index field in the query or field used in ORDER BY, the query is executed very quickly (not using filesort):

>EXPLAIN SELECT ObjectID, ObjectName FROM objects ORDER BY ObjectName;
id select_type table   type  possible_keys key          key_len ref  rows   Extra
1  SIMPLE      objects index NULL          ObjectName   387     NULL 122077 Using index

but if some other field (except for primary index field and ORDER BY field) is included in the field list (including *), MySQL uses filesort for some reason.

Please, help me to find out why and how to make the query (on the top) run quickly.

Thank you.

P.S. Table structure:

CREATE TABLE objects (
  ObjectID INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  ObjectTypeID SMALLINT(6) UNSIGNED DEFAULT NULL,
  ObjectName VARCHAR(128) COLLATE utf8_general_ci DEFAULT NULL,
  ObjectStateID TINYINT(3) UNSIGNED DEFAULT NULL,
  ObjectNotes LONGTEXT,
  ObjectParentID INTEGER(11) UNSIGNED DEFAULT NULL,
  ObjectCreationDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ObjectCode TEXT COLLATE utf8_unicode_ci,
  PRIMARY KEY (ObjectID),
  KEY ObjectTypeID (ObjectTypeID),
  KEY ObjectParentID (ObjectParentID),
  KEY ObjectName (ObjectName),
  CONSTRAINT ObjectType_FK FOREIGN KEY (ObjectTypeID) REFERENCES objecttypes (ObjectType_ID),
  CONSTRAINT ObjectState_FK FOREIGN KEY (ObjectStateID) REFERENCES objectstates (ObjectState_ID),
  CONSTRAINT Parent_FK FOREIGN KEY (ObjectParentID) REFERENCES objects (ObjectID),
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci';

Options: ReplyQuote


Subject Views Written By Posted
Query that returns indexed fields involves FILESORT, why? 3252 Yurij Zagrebnoy 02/26/2010 12:36PM
Re: Query that returns indexed fields involves FILESORT, why? 1803 Rick James 02/28/2010 01:36PM
Re: Query that returns indexed fields involves FILESORT, why? 1552 Yurij Zagrebnoy 03/01/2010 08:28AM
Re: Query that returns indexed fields involves FILESORT, why? 1676 Rick James 03/01/2010 01:16PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.