Vryjdfkk

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?

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/

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.