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
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.
order by kill the performance in MariaDB? And how can I optimize my view for this problem?
MySQL version: 5.5.5-10.11.3-MariaDB-1:10.11.3+maria~ubu2204 through PHP extension MySQLi