-1

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?

  • 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

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/

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.