+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=11)
+--- Thread: A more efficient pagination library? (/showthread.php?tid=70740)
A more efficient pagination library? - jhob - 05-23-2018
So the pagination library works great for working with smaller data sets. However I need to paginate a table of 500,000 records and the pagination is dogshit slow with some queries taking upwards of 30s to run.
I've been reading about it recently and am interested in implementing a more efficient pagination. Couple of articles here:
https://www.xarg.org/2011/10/optimized-pagination-using-mysql/
https://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/
Has anyone already implemented a faster pagination library for CodeIgniter before I embark on doing similar myself?
RE: A more efficient pagination library? - qury - 05-23-2018
I think it is not CI's pagination library that you have issue with, but the speed of your database query for pagination
RE: A more efficient pagination library? - InsiteFX - 05-24-2018
Those optimizations are fine for an existing database, but every database is different
and needs different optimizations. There is no one way to optimize them.
It's up to you to setup the right index keys and normalization of the database.
RE: A more efficient pagination library? - jhob - 05-29-2018
But even with an index pagination with that many records can be painfully slow. The alternative methods I mentioned all used the Id of the first/last items on the current page.
Anyway, by sounds of it there is no alternative pagination library already written so I will roll my own.
RE: A more efficient pagination library? - InsiteFX - 05-29-2018
If your going to roll your own then I would make it an Ajax Pagination Library.
RE: A more efficient pagination library? - php_rocs - 05-29-2018
@jhob
Something is wrong if you have 500K records and your queries are taking at least 30 seconds. You might want to think about doing a database analysis to see what can be done to speed up your queries.
RE: A more efficient pagination library? - php_rocs - 05-29-2018
@jhob,
I forgot to ask...what version of MySQL are you using? How are you connecting to the database ( MySQLi extension [the "i" stands for improved] or PDO [PHP Data Objects] )? Do you have a example query that takes more than 30 secs to execute.