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


| improve this question | | | | |
  • Please provide SHOW CREATE TABLE for each table. The EXPLAIN hints that you might have the best indexes, but we can't tell. – Rick James Mar 18 '19 at 16:34

Converting the query to cartesian form:

select `players`.`id`,
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;

allows to understand that you need in indices:

rooms (embed_code_id, type, id) or rooms (type, embed_code_id, id)

players (room_id, id, points) or players (room_id, points, id)

The index on rooms is effective. The index on players is covering.

| improve this answer | | | | |

You need a combined index on rooms(embed_code_id,type).

Only one index can be used at a time. type is a poor index.

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.