Changing ORDER BY direction causes performance issue I use MySQL 5.6, and I have two tables each with 16M rows:

CREATE TABLE IF NOT EXISTS `newsstudios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16855382 ;

CREATE TABLE IF NOT EXISTS `newsstudio_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `newsstudio_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `newsstudio_id` (`newsstudio_id`),
  KEY `category_id` (`category_id`),
  KEY `newsstudio_id_category_id` (`newsstudio_id`,`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16864013 ;


I have one query with order by order by newsstudios.id ASC :

SELECT SQL_NO_CACHE id FROM `newsstudios` WHERE exists 
(
  select newsstudio_id from newsstudio_categories 
  where newsstudios.id=newsstudio_categories.newsstudio_id 
  and newsstudio_categories.category_id in (1303,1313,1323,1333,1343,632)
) 
order by newsstudios.id limit 5;


the result of this query is:

+------+
| id   |
+------+
|   27 |
|   47 |
|   87 |
|  110 |
|  181 |
+------+
5 rows in set (0.19 sec)


but when I change the direction of order by to DESC the query execution time decreases 100 times:

+------+
| id   |
+------+
| 98232|
| 98111|
| 95222|
| 88132|
| 78181|
+------+
5 rows in set (21 sec)


First: why does this change in the direction of order by cause this huge difference in performance?

Second: before this query I have tried LEFT JOIN and WHERE IN queries instead of WHERE EXISTS but they have duplicate result that I should use GROUP BY that it cause using filesort and using temporary which decreases performance a lot. Do you have any suggestions for the query to have better performance?
If the different categories you are searching on are not to many and you can build the more complex query, this will use an index on (category_id, newsstudio_id) and I'd expect it to  be more efficient than your previous query, with either ASC or DESC choice.

It's a bit more complex to construct and I'd expect efficiency to degrade if the number of categories are tens or hundreds:

SELECT newsstudio_id
FROM 
  ( ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 1303
      ORDER BY newsstudio_id DESC  LIMIT 5 
    ) 
    UNION 
    ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 1313
      ORDER BY newsstudio_id DESC  LIMIT 5 
    )
      ...
    UNION
    ( SELECT newsstudio_id  FROM newsstudio_categories  WHERE category_id = 632
      ORDER BY newsstudio_id DESC  LIMIT 5 
    )
  ) AS tmp
ORDER BY newsstudio_id DESC
LIMIT 5 ;