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?