Pagination - many rows + offset = slow performance

Sounds more like a database problem than a code problem. Is the query slow if you execute it natively as well? Are the columns you are ordering by indexed?

When you get that much data a standard MySQL will slow down. Pagination by default gets a * count which is slower the counting just one column. With offsets same thing.

Basically when working with big data you have to be sure the database is cfg correctly and your queries need to be very exact. Also setting up proper caching helps tremendously via the database. Set up triggers that will cache either the whole query or parts of it that reset the cache on a row change or update.

Yep, this is a known MySQL thing. Using offset on large tables will cause this issue.

You can work around it by using "late row lookup". e.g.

SELECT *
FROM   products
JOIN (SELECT id
      FROM   products
      ORDER  BY date
      LIMIT  10000, 10) AS t ON t.id = products.id;

There are lots of articles on this topic but e.g. ahttp://devoluk.com/mysql-limit-offset-performance.html

Thank you guys! I was reading about the late row lookup however i solved the problem with count and cache the count of items and then create my own paginator which is limited for 500 pages. With this solution i dont have to care about performance and everything works fine even with using offset.

@miro4994

Glad we could help! Cache is amazing if you can set it up correctly.

@jekinney One more question to cache... I use redis driver for cache so it should store everything into the memory... Is there some way how to check how much memory is used for caching stored data, queries for example?

There is remote monitoring software that can display that information either on your sites backend or their website. I haven't personally implemented it as usually most hosting have some sort of monitoring system built in.

Hi @miro4994,

Would you mind sharing more details about how you solved this problem? Having same problem currently.