“排行榜”查詢的性能不佳

考慮以下 2 個表：

players:  | rooms: 
id        | id
name      | type
room_id   | embed_code_id
points    |

我的查詢需要從給定的房間類型和 embed_code_id 中獲取得分最高的 100 名玩家。

這是我目前擁有的：

   select `players`.`id`,
      `players`.`points`
from `players`
      inner join `rooms` on `players`.`room_id` = `rooms`.`id`
where `rooms`.`embed_code_id` = 678
 and `rooms`.`type` = 'single'
order by `players`.`points` desc, `players`.`id` asc
limit 100;

此查詢導致性能不佳（1.5 - 10 秒，取決於傳遞的參數）

注意：*兩個表都有大約 100 萬條記錄 *當我刪除ORDER BY子句時，時間下降到 100 毫秒以下 *我嘗試在 player.points 和 player.id 上設置索引，但沒有幫助 *我嘗試在 embed_code_id & 上設置索引類型也沒有幫助

EXPLAIN輸出表明我Using index; Using temporary; Using filesort不知道如何解決：

1   SIMPLE  rooms   NULL    ref PRIMARY,embed_code_id,type  type    6   const,const 478797  100.00  Using index; Using temporary; Using filesort
1   SIMPLE  players NULL    ref room_id room_id 4   playgo.rooms.id 1   100.00  NULL

這個查詢可以更有效還是我缺少一些索引？

謝謝

將查詢轉換為笛卡爾形式：

select `players`.`id`,
      `players`.`points`
from `players`, `rooms` 
where `players`.`room_id` = `rooms`.`id`
 and `rooms`.`embed_code_id` = 678
 and `rooms`.`type` = 'single'
order by `players`.`points` desc, `players`.`id` asc
limit 100;

允許了解您在索引中需要：

rooms (embed_code_id, type, id)或者rooms (type, embed_code_id, id)

players (room_id, id, points)或者players (room_id, points, id)

上的索引rooms是有效的。上的指數players是覆蓋。

引用自：https://dba.stackexchange.com/questions/232399