Why does Blaze-Persistence project count queries for pagination despite potential performance issues?

[gain7x]

I'm using Blaze-Persistence and noticed that its offset pagination feature projects the count query as follows( concisely reduced ):

select *, (select count(*) from Entity)
from Entity e inner join Sub s on e.sub_id=s.id limit 5;

Doesn't this count query executed for each row potentially cause performance issues? I'm curious about why Blaze-Persistence uses this approach. What are the advantages of inlining the count query, and in what scenarios does this method provide benefits?

I understand there is an option to disable this feature, but I am interested in why it is used.

Thank you for your insights!

[beikov]

The main idea of this is to avoid executing a second query for determining the page count, but as you noticed, this behavior can be configured.

Doesn't this count query executed for each row potentially cause performance issues?

If your database isn't capable of realizing that this is an uncorrelated subquery, this would indeed be a problem, but then your database query optimizer probably has a hard time with a lot of other things as well. Since the subquery is uncorrelated, it should only be executed once.

[gain7x]

Thank you for your response.

I understand now that this feature relies on the database's ability to cache scalar subqueries, and it is designed to be automatically enabled if the database supports such optimizations.

My service uses MariaDB 10.x, and since query caching is disabled, I have decided to disable the inline count query feature. Thank you for the clarification.