Possible Pagination Optimization

[rhegner]

Now that my database is growing, I see how heavy pagination actually is...

One reason for this is that NPoco seems to create unnecessary heavy queries to obtain the total item count.

Consider the following example:

var page = DB.QueryAsync<LoginLog>().Include(i => i.User).ToPage(1, 100);

NPoco creates a query with an unnecessary join to obtain the total number of items:

SELECT COUNT (*) FROM login_log LL LEFT JOIN user UI ON LL.user_id=UI.user_id

Interestingly, also MySQL does not seem to be able to optimize that unnecessary join away, so the query is really heavy on the database:

EXPLAIN ANALYZE:
-> Aggregate: count(0)  (cost=254649 rows=1) (actual time=534..534 rows=1 loops=1)
    -> Nested loop left join  (cost=235177 rows=194724) (actual time=0.108..517 rows=399592 loops=1)
        -> Table scan on LL  (cost=20980 rows=194724) (actual time=0.0753..142 rows=399592 loops=1)
        -> Single-row covering index lookup on UI using PRIMARY (user_id=LL.user_id)  (cost=1 rows=1) (actual time=741e-6..769e-6 rows=0.861 loops=399592)

So my proposal would be to remove that join when not needed to obtain the total number of items.

There are cases where the join cannot be optimized away (and that's where it probably gets tricky...), for example:

var page = DB.QueryAsync<LoginLog>().Include(i => i.User).Where(i => i.User.Age > 20).ToPage(1, 100);

Here the join is needed to determin the total number of items.