MySQL Very Slow Performance Using Order by Clause












0















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?










share|improve this question
















bumped to the homepage by Community 23 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
















0















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?










share|improve this question
















bumped to the homepage by Community 23 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














0












0








0








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?










share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Aug 5 '18 at 10:29









Husam Mohamed

4161313




4161313










asked Aug 3 '18 at 17:13









5a01d01P5a01d01P

61




61





bumped to the homepage by Community 23 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 23 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














  • 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










2 Answers
2






active

oldest

votes


















0














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/






share|improve this answer































    0














    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.






    share|improve this answer























      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
      });


      }
      });














      draft saved

      draft discarded


















      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









      0














      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/






      share|improve this answer




























        0














        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/






        share|improve this answer


























          0












          0








          0







          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/






          share|improve this answer













          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/







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Aug 14 '18 at 6:05









          danblackdanblack

          1,9561213




          1,9561213

























              0














              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.






              share|improve this answer




























                0














                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.






                share|improve this answer


























                  0












                  0








                  0







                  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.






                  share|improve this answer













                  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.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Aug 21 '18 at 21:38









                  Rick JamesRick James

                  43.1k22259




                  43.1k22259






























                      draft saved

                      draft discarded




















































                      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














                      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





















































                      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







                      Popular posts from this blog

                      Liste der Baudenkmale in Friedland (Mecklenburg)

                      Single-Malt-Whisky

                      Czorneboh