Consider the following 2 tables:

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

```

My query needs to fetch the 100 players with the most points from a given room type and embed\_code\_id.

here's what I have currently:

```
    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;

```

This query results in poor performance (1.5 - 10 seconds, depends on the parameters passed)

Notes:
\* Both tables have about 1M records
\* when i remove the `ORDER BY` clause, the time drops below 100ms
\* I tried setting an index on players.points & players.id, which didn't help
\* I tried setting an index on embed\_code\_id & type which also didn't help

The `EXPLAIN` output suggests `Using index; Using temporary; Using filesort` which i don't know how to fix:

```
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

```

Can this query be more efficient or i'm missing some index?

Thanks