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';