SELECT *
FROM `game` `t` 
JOIN
(
SELECT game_views.game_id, count(game_views.id) AS gv_cnt
FROM `game_views` `game_views` 
WHERE (`game_views`.`date` >  DATE(NOW() - INTERVAL 1 MONTH))
GROUP BY game_views.game_id
ORDER BY gv_cnt DESC
LIMIT 28 OFFSET 224
) as t1 ON (`t`.`id`=`t1`.`game_id`)

SELECT
t.id AS t0_c0,
t.catid AS t0_c1,
t.title AS t0_c2,
t.object AS t0_c3,
t.description AS t0_c4,
t.image AS t0_c5,
t.votes_summ AS t0_c6,
t.votes_count AS t0_c7,
t.views AS t0_c8,
t.favorites AS t0_c9,
t.date AS t0_c10,
t.is_uniq AS t0_c11,
count(game_views.id) AS gv_cnt,
category.id AS t1_c0,
category.slug AS t1_c1,
category.title AS t1_c2,
category.description AS t1_c3,
category.meta_title AS t1_c4,
category.parent AS t1_c5,
category.total_views AS t1_c6,
game_views.id AS t4_c0,
game_views.game_id AS t4_c1,
game_views.date AS t4_c2,
game_views.user_id AS t4_c3
FROM `game` `t`  
LEFT OUTER JOIN `category` `category` ON (`t`.`catid`=`category`.`id`)  
INNER JOIN `game_views` `game_views` ON (`game_views`.`game_id`=`t`.`id`)
WHERE (`game_views`.`date` > DATE(NOW() - INTERVAL 7 DAY))
GROUP BY game_views.game_id
ORDER BY gv_cnt DESC
LIMIT 28

SELECT *
FROM `game` `t` 
JOIN
(
SELECT game_views.game_id, count(*) AS gv_cnt
FROM `game_views` `game_views` 
WHERE (`game_views`.`date` >  DATE(NOW() - INTERVAL 1 MONTH))
GROUP BY game_views.game_id
ORDER BY gv_cnt DESC
LIMIT 28
) as t1 ON (`t`.`id`=`t1`.`game_id`);

SET SESSION max_heap_table_size=256000000

SELECT * 
FROM `game` `t` 
JOIN
(
SELECT * 
FROM game_views_popular_month 
ORDER BY game_views_popular_month.gv_cnt DESC
) 
as `t1` ON `t`.`id`=`t1`.`game_id`

SELECT * 
FROM `game` `t` 
JOIN
(
SELECT * 
FROM game_views_popular_month 
ORDER BY game_views_popular_month.gv_cnt DESC
) 
as `t1` ON `t`.`id`=`t1`.`game_id`

Или потому что таблицы у тебя с кукиш размером

а если напрямую сджойнить, без промежуточной материализации - не быстрее будет?

в первоначальном запросе было задействовано больше таблиц и присутствовал лимит.