Left join with same table making query too slow — MySQL
0
We have a table server_user_disk_usage which tracks the disk usage of users from different servers.
Query is to find sum of last disk usage of each user from different servers
SELECT
m1.user_name,
sum(m1.disk_usage) as disk_usage
FROM server_user_disk_usage m1
LEFT JOIN server_details s on s.id = m1.server_id
LEFT JOIN server_user_disk_usage m2
ON (m1.server_id = m2.server_id and m1.user_name = m2.user_name AND
m1.epoch < m2.epoch)
WHERE m2.epoch IS NULL
GROUP BY m1.user_name
order by cast(sum(m1.disk_usage) as unsigned) desc;
Response is correct but its too slow when moved to production because of huge data. Any help will be appreciated
mysql query-performance optimization
asked 21 mins ago
deepak premandeepak preman
11
New contributor
deepak preman is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
0
We have a table server_user_disk_usage which tracks the disk usage of users from different servers.
Query is to find sum of last disk usage of each user from different servers
SELECT
m1.user_name,
sum(m1.disk_usage) as disk_usage
FROM server_user_disk_usage m1
LEFT JOIN server_details s on s.id = m1.server_id
LEFT JOIN server_user_disk_usage m2
ON (m1.server_id = m2.server_id and m1.user_name = m2.user_name AND
m1.epoch < m2.epoch)
WHERE m2.epoch IS NULL
GROUP BY m1.user_name
order by cast(sum(m1.disk_usage) as unsigned) desc;
Response is correct but its too slow when moved to production because of huge data. Any help will be appreciated
mysql query-performance optimization
asked 21 mins ago
deepak premandeepak preman
11
New contributor
deepak preman is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Specify server version. 5+ or 8+?
– Akina
11 secs ago
add a comment |
0
0
0
We have a table server_user_disk_usage which tracks the disk usage of users from different servers.
Query is to find sum of last disk usage of each user from different servers
SELECT
m1.user_name,
sum(m1.disk_usage) as disk_usage
FROM server_user_disk_usage m1
LEFT JOIN server_details s on s.id = m1.server_id
LEFT JOIN server_user_disk_usage m2
ON (m1.server_id = m2.server_id and m1.user_name = m2.user_name AND
m1.epoch < m2.epoch)
WHERE m2.epoch IS NULL
GROUP BY m1.user_name
order by cast(sum(m1.disk_usage) as unsigned) desc;
Response is correct but its too slow when moved to production because of huge data. Any help will be appreciated
mysql query-performance optimization
asked 21 mins ago
deepak premandeepak preman
11
New contributor
deepak preman is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
We have a table server_user_disk_usage which tracks the disk usage of users from different servers.
Query is to find sum of last disk usage of each user from different servers
SELECT
m1.user_name,
sum(m1.disk_usage) as disk_usage
FROM server_user_disk_usage m1
LEFT JOIN server_details s on s.id = m1.server_id
LEFT JOIN server_user_disk_usage m2
ON (m1.server_id = m2.server_id and m1.user_name = m2.user_name AND
m1.epoch < m2.epoch)
WHERE m2.epoch IS NULL
GROUP BY m1.user_name
order by cast(sum(m1.disk_usage) as unsigned) desc;
Response is correct but its too slow when moved to production because of huge data. Any help will be appreciated
mysql query-performance optimization
mysql query-performance optimization
asked 21 mins ago
deepak premandeepak preman
11
New contributor
deepak preman is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 21 mins ago
deepak premandeepak preman
11
New contributor
deepak preman is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 21 mins ago
deepak premandeepak preman
11
New contributor
deepak preman is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 21 mins ago
deepak premandeepak preman
11
asked 21 mins ago
deepak premandeepak preman
11
11
New contributor
deepak preman is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
deepak preman is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
deepak preman is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Specify server version. 5+ or 8+?
– Akina
11 secs ago
add a comment |
Specify server version. 5+ or 8+?
– Akina
11 secs ago
Specify server version. 5+ or 8+?
– Akina
11 secs ago
Specify server version. 5+ or 8+?
– Akina
11 secs ago
add a comment |
0
active
oldest
votes
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
});
}
});
deepak preman is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
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%2f229701%2fleft-join-with-same-table-making-query-too-slow-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
deepak preman is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
deepak preman is a new contributor. Be nice, and check out our Code of Conduct.
deepak preman is a new contributor. Be nice, and check out our Code of Conduct.
deepak preman is a new contributor. Be nice, and check out our Code of Conduct.
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.
draft saved
draft discarded
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%2f229701%2fleft-join-with-same-table-making-query-too-slow-mysql%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
Specify server version. 5+ or 8+?
– Akina
11 secs ago