SQLite: How to fetch the ORDER index of COUNT

I have an SQLite database that contains a list of user messages in a group. And I want to get a user's "rank" by counting the number of messages they had sent.

Currently I'm doing this

SELECT user_id, COUNT(*) as count 
FROM message
group by user_id
ORDER BY count DESC

It'd return something like this:

-	user_id	count
1	2072040132	61877
2	1609505732	40514
3	1543287045	34735
4	203349203	30570
5	842634673	29651
6	1702633101	29185
7	1978947042	27728
8	1929648593	27025
9	1069841429	17944
10	1437208364	17344
11	...	...

Like user 1609505732 is top 2, and 1702633101 is top 6.

But my database has more than 2 million rows, and this is too slow having to fetch all of the list. I was wondering if there are any way that I can fetch only the order of it.

Like this:

-	user_id	order	count
1	1702633101	6	61877

And the user with id=1702633101 is top 6. That'd be a lot faster.

Thanks for spending time on my question, I can't seem to find the answer anywhere on the internet.

Solution

To improve query speed, I'd consider physicalising the aggregate view, example below:


CREATE Table as tbl_aggregate()
Id INTEGER PRIMARY KEY AUTOINCREMENT
, user_id NVARCHAR
, count INT;



INSERT INTO tbl_aggregate 
SELECT user_id, COUNT(*) as count 
FROM message
group by user_id
ORDER BY count DESC;

Select * from tbl_aggregate
Where Id = 6

Select top 10 * from tbl_aggregate