I have done some performance tests (in frontend) but this will be the same for backend too.
There are 2 queries, because:
- first you need to load all items on the page (e.g. 20 items when the pagination is 0 - 20)
- but because of pagination, you need to get info about count of all items. This is the second query - to successfully create pagination output.
The problem:
- the query is changing by different parameters (mostly set by user) - so it cannot be used the count of all items in database
- the query does include "group by" which means, it cannot by used with mysql "COUNT()" which is fast. Yes there are possible ways to run the query with "COUNT()" and "GROUP BY" but regarding performance then there is no difference in comparing to loading all rows.
So there can be a small performance improving I will test to implement.
It is not possible to use "COUNT()" so there is still the only way to load all rows. But in some cases we can differentiate between loading all rows for counting and loading all rows for displaying the output.
In output we need more columns.
In count we can use only one, mostly ID column (tested the ordering too, but seems ordering does not have any influence on the performance, when only one column is loaded)
So in some of next version I will try to change the loading of rows for the count. It will be not so fast as COUNT() but at least it will be faster then loading more columns.
Jan