search

ThingEngineer / PHP-MySQLi-Database-Class

Wrapper for a PHP MySQL class, which utilizes MySQLi and prepared statements.
Other
3.29k stars 1.35k forks source link

pagination performance on large datasets #963

Open boicetomlin opened 3 years ago

boicetomlin commented 3 years ago

On large datasets using SQL_CALC_FOUND_ROWS produces slow results. it would be nice to have a way to select a page of results and issue a separate query to get the number of found rows. ordering, joining in combination with SQL_CALC_FOUND_ROWS produces even slower results. I'm seeing much faster performance by issuing separate queries for the limited result set and then another for the count result. however the class does not appear to offer this ability out of the box. the get() method only allows me to limit the results not select a limit range and the paginate method uses SQL_CALC_FOUND_ROWS without an option to turn it off. I would love to work on a modification to this class that offered a few different methods of pagination that addressed performance issues on large datasets.

mrjk990 commented 11 months ago

i have same issue pagination so slow with 700k records do you find a solution ?

boicetomlin commented 11 months ago

my solution was to first disable SQL_CALC_FOUND_ROWS. then do a query to select the data and another query with the same conditions to select the count.