Mysql Group by rank() performance

Question

I'm currently working on a query, but I'm not getting the speed so I'm asking a question.

What I want to find is 'count the most names in each category by DataID'.

Data_Category { id : INT PRIMARY KEY AUTO_INCREMENT, DataId: INT, name: varchar2(200), category: varchar2(200) }

and it is possible to input in duplicate.

select dataId, name, category, count(*) as cnt
, rank() over (partition by dataId, category order by count(*) desc) as "ranking"
from data_category
group by dataId, name, category

In the above query, there is a difference in speed with and without the rank part.

If there is no rank, it takes 0.0053, and if there is, it takes 0.5 seconds.

select *, rank() over (partition by A.dataId, A.category order by cnt desc) as "ranking"
from(
  select dataId, name, category, count(*) as cnt
  from data_category
  group by dataId, name, category
) A

The above query also yields almost the same speed. The moment it is wrapped in a select statement, it is slowing down due to the temporary table creation.

The data is about 400,000 cases.

The index is (dataId, category, name) .

I want to extract 5 names with the highest number of names in each category by data ID, but I can't figure out the direction of the query.

And I will create 'view' using this query.

Even if you don't write a query, I sincerely ask for a simple hint..!

Output:

dataId	category	name	rank
1	cate1	name1_1	1
1	cate1	name1_2	2
1	cate1	name1_5	3
1	cate1	name1_3	4
1	cate2	name2_1	1
1	cate2	name2_5	2
1	cate2	name2_3	3
2	cate1	name3_1	1
2	cate3	name3_9	1

Thank you.