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
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 |
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.