Poor performance on a 'leaderboard' query
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
mysql index join order-by
asked 4 mins ago
Idan PtichiIdan Ptichi
31
add a comment |
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
mysql index join order-by
asked 4 mins ago
Idan PtichiIdan Ptichi
31
add a comment |
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
mysql index join order-by
asked 4 mins ago
Idan PtichiIdan Ptichi
31
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
mysql index join order-by
mysql index join order-by
asked 4 mins ago
Idan PtichiIdan Ptichi
31
asked 4 mins ago
Idan PtichiIdan Ptichi
31
asked 4 mins ago
Idan PtichiIdan Ptichi
31
asked 4 mins ago
Idan PtichiIdan Ptichi
31
asked 4 mins ago
Idan PtichiIdan Ptichi
31
31
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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.
answered 33 secs ago
danblackdanblack
2,1061214
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232399%2fpoor-performance-on-a-leaderboard-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered 33 secs ago
danblackdanblack
2,1061214
add a comment |
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.
answered 33 secs ago
danblackdanblack
2,1061214
add a comment |
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.
answered 33 secs ago
danblackdanblack
2,1061214
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.
answered 33 secs ago
danblackdanblack
2,1061214
answered 33 secs ago
danblackdanblack
2,1061214
answered 33 secs ago
danblackdanblack
2,1061214
answered 33 secs ago
danblackdanblack
2,1061214
2,1061214
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232399%2fpoor-performance-on-a-leaderboard-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
