MySQL Forums
Forum List  »  Optimizer & Parser

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?
3362
February 26, 2010 12:36PM


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.