MySQL Very Slow Performance Using Order by Clause#1093 - You can't specify target table 'R' for update in...
Where is the line between being obedient and getting bullied by a boss?
Don't know what I’m looking for regarding removable HDDs?
Why do phishing e-mails use faked e-mail addresses instead of the real one?
How can atoms be electrically neutral when there is a difference in the positions of the charges?
The need of reserving one's ability in job interviews
What Does the Heart In Gyms Mean?
How to evaluate the limit where something is raised to a power of x?
What is a term for a function that when called repeatedly, has the same effect as calling once?
Is there a frame of reference in which I was born before I was conceived?
Skis versus snow shoes - when to choose which for travelling the backcountry?
Dystopian novel where telepathic humans live under a dome
It took me a lot of time to make this, pls like. (YouTube Comments #1)
Can a space-faring robot still function over a billion years?
Six real numbers so that product of any five is the sixth one
Did Amazon pay $0 in taxes last year?
How do you say "powers of ten"?
How to make a *empty* field behaves like a *null* field when it comes to standard values?
Plagiarism of code by other PhD student
Pure Functions: Does "No Side Effects" Imply "Always Same Output, Given Same Input"?
Roots of 6th chords on the guitar for different inversions/voicings
Can I become debt free or should I file for bankruptcy? How do I manage my debt and finances?
Why do members of Congress in committee hearings ask witnesses the same question multiple times?
Why can't we make a perpetual motion machine by using a magnet to pull up a piece of metal, then letting it fall back down?
Sometimes a banana is just a banana
MySQL Very Slow Performance Using Order by Clause
#1093 - You can't specify target table 'R' for update in FROM clauseSimple query is slow on 4M-rows tableMySQL : Avoid Temporary/Filesort Caused by GROUP BY Clause in ViewsIs a update-only-once-row table worth sharding?Optimizing a simple query on a large tableMySQL query taking too longError in creating table in MySQL v5.5.45 - Invalild default value for columnselect MAX() from MySQL view (2x INNER JOIN) is slowWhy is this query with WHERE, ORDER BY and LIMIT so slow?What does 'innodb_buffer_pool_reads' and 'innodb_buffer_pool_read_requests' actually mean?
I have a one table with millions of entry.Below is table structure.
CREATE TABLE `useractivity` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`userid` bigint(20) NOT NULL,
`likes` bigint(20) DEFAULT NULL,
`views` bigint(20) DEFAULT NULL,
`shares` bigint(20) DEFAULT NULL,
`totalcount` bigint(20) DEFAULT NULL,
`status` bigint(20) DEFAULT NULL,
`createdat` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `userid` (`userid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
And Below is query in which I am getting slow performance.
SELECT userid,
(sum(likes)+SUM(views)+SUM(shares)+SUM(totalcount)+SUM(`status`)) as total
from useractivity
GROUP BY userid
ORDER BY total DESC
limit 0, 20;
When I am executing above query without ORDER BY then it gives me fast result set But when using ORDER BY then this query became slow,though I used limit for pagination.
What can I do to speed up this query?
mysql query-performance group-by order-by
edited Aug 5 '18 at 10:29
Husam Mohamed
4161313
asked Aug 3 '18 at 17:13
5a01d01P5a01d01P
61
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have a one table with millions of entry.Below is table structure.
CREATE TABLE `useractivity` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`userid` bigint(20) NOT NULL,
`likes` bigint(20) DEFAULT NULL,
`views` bigint(20) DEFAULT NULL,
`shares` bigint(20) DEFAULT NULL,
`totalcount` bigint(20) DEFAULT NULL,
`status` bigint(20) DEFAULT NULL,
`createdat` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `userid` (`userid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
And Below is query in which I am getting slow performance.
SELECT userid,
(sum(likes)+SUM(views)+SUM(shares)+SUM(totalcount)+SUM(`status`)) as total
from useractivity
GROUP BY userid
ORDER BY total DESC
limit 0, 20;
When I am executing above query without ORDER BY then it gives me fast result set But when using ORDER BY then this query became slow,though I used limit for pagination.
What can I do to speed up this query?
mysql query-performance group-by order-by
edited Aug 5 '18 at 10:29
Husam Mohamed
4161313
asked Aug 3 '18 at 17:13
5a01d01P5a01d01P
61
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
With the order by for total it needs to calculate the total for all users before the first 20 can be chosen. Without the order by any 20 users can be returned
– Lennart
Aug 3 '18 at 18:04
Please don't cross post.
– sticky bit
Aug 3 '18 at 18:33
2
Off topic, but why isn't userid unique? Furthermore, why are likes, views, shares, etc nullable. Should they not be DEFAULT 0 NOT NULL?
– Lennart
Aug 3 '18 at 20:25
nothing is going to make that query fast, you will need to use a different query
– Jasen
Aug 3 '18 at 20:53
@Jasen.any query in your mind that give me faster performance?
– 5a01d01P
Aug 4 '18 at 5:10
add a comment |
I have a one table with millions of entry.Below is table structure.
CREATE TABLE `useractivity` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`userid` bigint(20) NOT NULL,
`likes` bigint(20) DEFAULT NULL,
`views` bigint(20) DEFAULT NULL,
`shares` bigint(20) DEFAULT NULL,
`totalcount` bigint(20) DEFAULT NULL,
`status` bigint(20) DEFAULT NULL,
`createdat` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `userid` (`userid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
And Below is query in which I am getting slow performance.
SELECT userid,
(sum(likes)+SUM(views)+SUM(shares)+SUM(totalcount)+SUM(`status`)) as total
from useractivity
GROUP BY userid
ORDER BY total DESC
limit 0, 20;
When I am executing above query without ORDER BY then it gives me fast result set But when using ORDER BY then this query became slow,though I used limit for pagination.
What can I do to speed up this query?
mysql query-performance group-by order-by
edited Aug 5 '18 at 10:29
Husam Mohamed
4161313
asked Aug 3 '18 at 17:13
5a01d01P5a01d01P
61
I have a one table with millions of entry.Below is table structure.
CREATE TABLE `useractivity` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`userid` bigint(20) NOT NULL,
`likes` bigint(20) DEFAULT NULL,
`views` bigint(20) DEFAULT NULL,
`shares` bigint(20) DEFAULT NULL,
`totalcount` bigint(20) DEFAULT NULL,
`status` bigint(20) DEFAULT NULL,
`createdat` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `userid` (`userid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
And Below is query in which I am getting slow performance.
SELECT userid,
(sum(likes)+SUM(views)+SUM(shares)+SUM(totalcount)+SUM(`status`)) as total
from useractivity
GROUP BY userid
ORDER BY total DESC
limit 0, 20;
When I am executing above query without ORDER BY then it gives me fast result set But when using ORDER BY then this query became slow,though I used limit for pagination.
What can I do to speed up this query?
mysql query-performance group-by order-by
mysql query-performance group-by order-by
edited Aug 5 '18 at 10:29
Husam Mohamed
4161313
asked Aug 3 '18 at 17:13
5a01d01P5a01d01P
61
edited Aug 5 '18 at 10:29
Husam Mohamed
4161313
asked Aug 3 '18 at 17:13
5a01d01P5a01d01P
61
edited Aug 5 '18 at 10:29
Husam Mohamed
4161313
edited Aug 5 '18 at 10:29
Husam Mohamed
4161313
edited Aug 5 '18 at 10:29
Husam Mohamed
4161313
4161313
asked Aug 3 '18 at 17:13
5a01d01P5a01d01P
61
asked Aug 3 '18 at 17:13
5a01d01P5a01d01P
61
asked Aug 3 '18 at 17:13
5a01d01P5a01d01P
61
61
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
With the order by for total it needs to calculate the total for all users before the first 20 can be chosen. Without the order by any 20 users can be returned
– Lennart
Aug 3 '18 at 18:04
Please don't cross post.
– sticky bit
Aug 3 '18 at 18:33
2
Off topic, but why isn't userid unique? Furthermore, why are likes, views, shares, etc nullable. Should they not be DEFAULT 0 NOT NULL?
– Lennart
Aug 3 '18 at 20:25
nothing is going to make that query fast, you will need to use a different query
– Jasen
Aug 3 '18 at 20:53
@Jasen.any query in your mind that give me faster performance?
– 5a01d01P
Aug 4 '18 at 5:10
add a comment |
1
With the order by for total it needs to calculate the total for all users before the first 20 can be chosen. Without the order by any 20 users can be returned
– Lennart
Aug 3 '18 at 18:04
Please don't cross post.
– sticky bit
Aug 3 '18 at 18:33
2
Off topic, but why isn't userid unique? Furthermore, why are likes, views, shares, etc nullable. Should they not be DEFAULT 0 NOT NULL?
– Lennart
Aug 3 '18 at 20:25
nothing is going to make that query fast, you will need to use a different query
– Jasen
Aug 3 '18 at 20:53
@Jasen.any query in your mind that give me faster performance?
– 5a01d01P
Aug 4 '18 at 5:10
1
1
With the order by for total it needs to calculate the total for all users before the first 20 can be chosen. Without the order by any 20 users can be returned
– Lennart
Aug 3 '18 at 18:04
With the order by for total it needs to calculate the total for all users before the first 20 can be chosen. Without the order by any 20 users can be returned
– Lennart
Aug 3 '18 at 18:04
Please don't cross post.
– sticky bit
Aug 3 '18 at 18:33
Please don't cross post.
– sticky bit
Aug 3 '18 at 18:33
2
2
Off topic, but why isn't userid unique? Furthermore, why are likes, views, shares, etc nullable. Should they not be DEFAULT 0 NOT NULL?
– Lennart
Aug 3 '18 at 20:25
Off topic, but why isn't userid unique? Furthermore, why are likes, views, shares, etc nullable. Should they not be DEFAULT 0 NOT NULL?
– Lennart
Aug 3 '18 at 20:25
nothing is going to make that query fast, you will need to use a different query
– Jasen
Aug 3 '18 at 20:53
nothing is going to make that query fast, you will need to use a different query
– Jasen
Aug 3 '18 at 20:53
@Jasen.any query in your mind that give me faster performance?
– 5a01d01P
Aug 4 '18 at 5:10
@Jasen.any query in your mind that give me faster performance?
– 5a01d01P
Aug 4 '18 at 5:10
add a comment |
2 Answers
2
active
oldest
votes
You'll need to store a total of likes, shares etc in the users table. Use a generated column in the this table to come up with the total and make it indexed:
ALTER TABLE users
ADD likes BIGINT UNSIGNED NOT NULL,
ADD views BIGINT UNSIGNED NOT NULL,
ADD shares BIGINT UNSIGNED NOT NULL,
ADD totalcount BIGINT UNSIGNED NOT NULL,
ADD status BIGINT UNSIGNED NOT NULL,
ADD total BIGINT UNSIGNED GENERATED ALWAYS AS (likes + views + shares + totalcount + status),
ADD KEY key_total (total);
The query below will use the index:
explain SELECT id, total
FROM users
ORDER BY total DESC
LIMIT 0, 20;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| --- | ----------- | ----- | ---------- | ----- | ------------- | --------- | ------- | --- | ---- | -------- | ----------- |
| 1 | SIMPLE | users | | index | | key_total | 9 | | 1 | 100 | Using index |
https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html
https://mariadb.com/kb/en/library/generated-columns/
answered Aug 14 '18 at 6:05
danblackdanblack
1,9561213
add a comment |
Huh? What is the table for? Someone does a LIKE, so one more row is created, with a bunch of zeros and a 1, plus a timestamp?
Plan A: Increment a table with one row per user. Then no SUM() is needed.
Plan B: Build and maintain a Summary Table. That way you could also get the number of Views (etc), by user, for a given day or week or whatever.
answered Aug 21 '18 at 21:38
Rick JamesRick James
43.1k22259
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%2f214022%2fmysql-very-slow-performance-using-order-by-clause%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You'll need to store a total of likes, shares etc in the users table. Use a generated column in the this table to come up with the total and make it indexed:
ALTER TABLE users
ADD likes BIGINT UNSIGNED NOT NULL,
ADD views BIGINT UNSIGNED NOT NULL,
ADD shares BIGINT UNSIGNED NOT NULL,
ADD totalcount BIGINT UNSIGNED NOT NULL,
ADD status BIGINT UNSIGNED NOT NULL,
ADD total BIGINT UNSIGNED GENERATED ALWAYS AS (likes + views + shares + totalcount + status),
ADD KEY key_total (total);
The query below will use the index:
explain SELECT id, total
FROM users
ORDER BY total DESC
LIMIT 0, 20;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| --- | ----------- | ----- | ---------- | ----- | ------------- | --------- | ------- | --- | ---- | -------- | ----------- |
| 1 | SIMPLE | users | | index | | key_total | 9 | | 1 | 100 | Using index |
https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html
https://mariadb.com/kb/en/library/generated-columns/
answered Aug 14 '18 at 6:05
danblackdanblack
1,9561213
add a comment |
You'll need to store a total of likes, shares etc in the users table. Use a generated column in the this table to come up with the total and make it indexed:
ALTER TABLE users
ADD likes BIGINT UNSIGNED NOT NULL,
ADD views BIGINT UNSIGNED NOT NULL,
ADD shares BIGINT UNSIGNED NOT NULL,
ADD totalcount BIGINT UNSIGNED NOT NULL,
ADD status BIGINT UNSIGNED NOT NULL,
ADD total BIGINT UNSIGNED GENERATED ALWAYS AS (likes + views + shares + totalcount + status),
ADD KEY key_total (total);
The query below will use the index:
explain SELECT id, total
FROM users
ORDER BY total DESC
LIMIT 0, 20;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| --- | ----------- | ----- | ---------- | ----- | ------------- | --------- | ------- | --- | ---- | -------- | ----------- |
| 1 | SIMPLE | users | | index | | key_total | 9 | | 1 | 100 | Using index |
https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html
https://mariadb.com/kb/en/library/generated-columns/
answered Aug 14 '18 at 6:05
danblackdanblack
1,9561213
add a comment |
You'll need to store a total of likes, shares etc in the users table. Use a generated column in the this table to come up with the total and make it indexed:
ALTER TABLE users
ADD likes BIGINT UNSIGNED NOT NULL,
ADD views BIGINT UNSIGNED NOT NULL,
ADD shares BIGINT UNSIGNED NOT NULL,
ADD totalcount BIGINT UNSIGNED NOT NULL,
ADD status BIGINT UNSIGNED NOT NULL,
ADD total BIGINT UNSIGNED GENERATED ALWAYS AS (likes + views + shares + totalcount + status),
ADD KEY key_total (total);
The query below will use the index:
explain SELECT id, total
FROM users
ORDER BY total DESC
LIMIT 0, 20;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| --- | ----------- | ----- | ---------- | ----- | ------------- | --------- | ------- | --- | ---- | -------- | ----------- |
| 1 | SIMPLE | users | | index | | key_total | 9 | | 1 | 100 | Using index |
https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html
https://mariadb.com/kb/en/library/generated-columns/
answered Aug 14 '18 at 6:05
danblackdanblack
1,9561213
You'll need to store a total of likes, shares etc in the users table. Use a generated column in the this table to come up with the total and make it indexed:
ALTER TABLE users
ADD likes BIGINT UNSIGNED NOT NULL,
ADD views BIGINT UNSIGNED NOT NULL,
ADD shares BIGINT UNSIGNED NOT NULL,
ADD totalcount BIGINT UNSIGNED NOT NULL,
ADD status BIGINT UNSIGNED NOT NULL,
ADD total BIGINT UNSIGNED GENERATED ALWAYS AS (likes + views + shares + totalcount + status),
ADD KEY key_total (total);
The query below will use the index:
explain SELECT id, total
FROM users
ORDER BY total DESC
LIMIT 0, 20;
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| --- | ----------- | ----- | ---------- | ----- | ------------- | --------- | ------- | --- | ---- | -------- | ----------- |
| 1 | SIMPLE | users | | index | | key_total | 9 | | 1 | 100 | Using index |
https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html
https://mariadb.com/kb/en/library/generated-columns/
answered Aug 14 '18 at 6:05
danblackdanblack
1,9561213
answered Aug 14 '18 at 6:05
danblackdanblack
1,9561213
answered Aug 14 '18 at 6:05
danblackdanblack
1,9561213
answered Aug 14 '18 at 6:05
danblackdanblack
1,9561213
1,9561213
add a comment |
add a comment |
Huh? What is the table for? Someone does a LIKE, so one more row is created, with a bunch of zeros and a 1, plus a timestamp?
Plan A: Increment a table with one row per user. Then no SUM() is needed.
Plan B: Build and maintain a Summary Table. That way you could also get the number of Views (etc), by user, for a given day or week or whatever.
answered Aug 21 '18 at 21:38
Rick JamesRick James
43.1k22259
add a comment |
Huh? What is the table for? Someone does a LIKE, so one more row is created, with a bunch of zeros and a 1, plus a timestamp?
Plan A: Increment a table with one row per user. Then no SUM() is needed.
Plan B: Build and maintain a Summary Table. That way you could also get the number of Views (etc), by user, for a given day or week or whatever.
answered Aug 21 '18 at 21:38
Rick JamesRick James
43.1k22259
add a comment |
Huh? What is the table for? Someone does a LIKE, so one more row is created, with a bunch of zeros and a 1, plus a timestamp?
Plan A: Increment a table with one row per user. Then no SUM() is needed.
Plan B: Build and maintain a Summary Table. That way you could also get the number of Views (etc), by user, for a given day or week or whatever.
answered Aug 21 '18 at 21:38
Rick JamesRick James
43.1k22259
Huh? What is the table for? Someone does a LIKE, so one more row is created, with a bunch of zeros and a 1, plus a timestamp?
Plan A: Increment a table with one row per user. Then no SUM() is needed.
Plan B: Build and maintain a Summary Table. That way you could also get the number of Views (etc), by user, for a given day or week or whatever.
answered Aug 21 '18 at 21:38
Rick JamesRick James
43.1k22259
answered Aug 21 '18 at 21:38
Rick JamesRick James
43.1k22259
answered Aug 21 '18 at 21:38
Rick JamesRick James
43.1k22259
answered Aug 21 '18 at 21:38
Rick JamesRick James
43.1k22259
43.1k22259
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%2f214022%2fmysql-very-slow-performance-using-order-by-clause%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
1
With the order by for total it needs to calculate the total for all users before the first 20 can be chosen. Without the order by any 20 users can be returned
– Lennart
Aug 3 '18 at 18:04
Please don't cross post.
– sticky bit
Aug 3 '18 at 18:33
2
Off topic, but why isn't userid unique? Furthermore, why are likes, views, shares, etc nullable. Should they not be DEFAULT 0 NOT NULL?
– Lennart
Aug 3 '18 at 20:25
nothing is going to make that query fast, you will need to use a different query
– Jasen
Aug 3 '18 at 20:53
@Jasen.any query in your mind that give me faster performance?
– 5a01d01P
Aug 4 '18 at 5:10