SQL Query optimization to avoid temporary table

mysql, sql

Solution

As you probably understand, the problem is that `GROUP BY` orders data by `UserId`, but the resulted set should be ordered by `Created`; thus, MySQL puts the output in a temporary table, sorts it and outputs.

The trick might be to force outputing distinct rows in the `Created` order at once.

The first that comes to my mind is something like this:

SELECT DISTINCT
  UserId,
  FriendUserId,
  UNIX_TIMESTAMP(`Created`) AS `Created`
FROM T1
WHERE FriendUserId = 22 AND IsDisplayed = 0
ORDER BY `Created`

and change the index to `(FriendUserId, IsDisplayed, Created, UserId)`.

Or another query with the same index:

SELECT
  UserId,
  FriendUserId,
  UNIX_TIMESTAMP(`Created`) AS `Created`
FROM T1
WHERE FriendUserId = 22 AND IsDisplayed = 0
GROUP BY `Created`, UserId

Problem

The Table: ``` CREATE TABLE `T1` ( `UserId` int(10) unsigned NOT NULL, `FriendUserId` int(10) unsigned NOT NULL, `IsDisplayed` tinyint(1) unsigned NOT NULL, `Created` datetime NOT NULL, KEY `FriendUserId` (`FriendUserId`,`IsDisplayed`,`UserId`,`Created`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; ``` The Query: ``` SELECT `UserId`, `FriendUserId`, UNIX_TIMESTAMP(`Created`) AS `Created` FROM `T1` WHERE `FriendUserId` = 22 AND `IsDisplayed` = 0 GROUP BY `UserId` ORDER BY `Created` ``` EXPLAIN result: ``` id: 1 select_type: SIMPLE table: T1 type: ref possible_keys: FriendUserId key: FriendUserId key_len: 5 ref: const,const rows: 1 Extra: Using where; Using index; Using temporary; Using filesort ``` Question: How can I optimize it so that no temporary table is used?

Original source