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)
* 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
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?