How can I optimize the view in MariaDB for Order By?

Question

I have a list of products. Each has many tags. Those tags have normal tables, but they also have OLAP tables that present CSV for each product.

For example, a chair can have 'furniture', 'wood', and 'fire-prone' tags.

Thus a table exists that calculates CSV for tags as ProductId, TagCsv:

1   furniture,wood,fire-prone

Based on this design, I have this query:

select
    p.*,
    t.Csv as TagsCsv
from Products.Products p
left join Products.TagCsvs t
on p.Id = t.ProductId

This view enables the search on tags and combines that search with product title, and pricing and performs sorting and pagination, etc.

And it works very fast as long as we do not sort. However, when we sort it, it becomes prolonged.

I saw a couple of questions on this site regarding slow performance for order by, but I did not understand them.

The problem is that even adding order by p.Id kills the performance. While the p.Id is the primary key of the Products table. Why?

We were using Microsoft SQL Server and we did not have this issue there. We migrated to MariaDB and now we face this problem.

Why does order by kill the performance in MariaDB? And how can I optimize my view for this problem?

My env:
MySQL version: 5.5.5-10.11.3-MariaDB-1:10.11.3+maria~ubu2204 through PHP extension MySQLi