SQL Query optimization to avoid temporary table

Question

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?

Solution 1

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

Solution 2

MySQL documentation says:

Temporary tables can be created under conditions such as these:

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

So, you can only avoid of using temporary table only by removing order by Created

Comments

[yeahman]

I don't understand... it is no more ordered by created.... with the modifications you made...