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:

    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

  • There's not necessarily a difference between SQL Server and MariaDB that makes ORDER BY worse in MariaDB. But in order for us to be able to help you, we'd need to see the exact slow query, the EXPLAIN ANALYZE for it (query plan), and the index definitions on your tables.
    – J.D.
    Sep 10 at 12:37
  • @J.D. The scripts would be long. What options do I have for this? A good online fiddle for MariaDB? A simple text file uploaded somewhere? Sep 10 at 13:02
  • You're saying the specific SELECT statement with an ORDER BY clause is long?...because the example you gave so far is quite short. If so, then dbfiddle.uk would work (and is always welcomed anyway).
    – J.D.
    Sep 10 at 13:57
  • Look at EXPLAIN {query} on both SQL Server and MariaDB
    – danblack
    Sep 11 at 1:56
  • Too vague. Please show us the table structures (SHOW CREATE TABLE) and the problematic query (SELECT ... ORDER BY ...). Also indicate how many rows (approx) in each table.
    – Rick James
    Sep 20 at 17:52


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.