MySQL ORDER BY performs better with a SUBQUERY than a PLAIN simple query

Question

For some reason I don't understand this plain regular MySQL query runs SLOWER than expected. From what I understood, MySQL would first slice the queryset using WHERE then sort the subset of results. The impression I get is something different (?). Perhaps the output of the "optimized" query is NOT trustable to be accurate even though it's returning the same row. The same query without the ORDER BY clause runs really fast.

Server version:     5.6.10 MySQL Community Server (GPL)

This is the query:

SELECT *
FROM `core_tweet`
WHERE `core_tweet`.`streamer_id` = 44566
ORDER BY `platform_id`
LIMIT 1;
1 row in set (27.84 sec)
rerun with SQL_NO_CACHE (46.39 sec)

This is the now "optimized" query:

SELECT *
FROM  (
  SELECT *
  FROM `core_tweet`
  WHERE `core_tweet`.`streamer_id` = 44566
) sub
ORDER BY sub.platform_id ASC
LIMIT 1;
1 row in set (0.56 sec)
rerun with SQL_NO_CACHE (3.82 sec)

And their respective EXPLAIN outputs:

+----+-------------+------------+-------+-----------------------------------------------------+-------------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys                                       | key         | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+-----------------------------------------------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | core_tweet | index | core_tweet_streamer_id_51082c18_fk_core_streamer_id | platform_id | 402     | NULL |  462 | Using where |
+----+-------------+------------+-------+-----------------------------------------------------+-------------+---------+------+------+-------------+


+----+-------------+------------+------+-----------------------------------------------------+-----------------------------------------------------+---------+-------+-------+----------------+
| id | select_type | table      | type | possible_keys                                       | key                                                 | key_len | ref   | rows  | Extra          |
+----+-------------+------------+------+-----------------------------------------------------+-----------------------------------------------------+---------+-------+-------+----------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL                                                | NULL                                                | NULL    | NULL  | 52072 | Using filesort |
|  2 | DERIVED     | core_tweet | ref  | core_tweet_streamer_id_51082c18_fk_core_streamer_id | core_tweet_streamer_id_51082c18_fk_core_streamer_id | 5       | const | 52072 | NULL           |
+----+-------------+------------+------+-----------------------------------------------------+-----------------------------------------------------+---------+-------+-------+----------------+

This is the table:

CREATE TABLE `core_tweet` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `published_at` datetime(6) NOT NULL,
  `platform_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `text` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `retweet_count` bigint(20) DEFAULT NULL,
  `favorite_count` bigint(20) DEFAULT NULL,
  `hashtags` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tweet_url` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `streamer_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `platform_id` (`platform_id`),
  KEY `core_tweet_streamer_id_51082c18_fk_core_streamer_id` (`streamer_id`),
  CONSTRAINT `core_tweet_streamer_id_51082c18_fk_core_streamer_id` FOREIGN KEY (`streamer_id`) REFERENCES `core_streamer` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26267613 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Tables stats:

core_tweet row count: 25856893
core_tweet `WHERE streamer_id = 44566` row count: 25868
core_streamer row count: 47203

Answer 1

This may allow even the first version to run faster yet:

Replace KEY(streamer_id) with

INDEX(streamer_id, platform_id)