Codeigniter pagination avoid LIMIT with OFFSET

1
votes

Is there anyway to avoid using LIMIT $page,$offset when retrieving rows in codeigniter pagination?

When i use limit with offset on milions records when retrieving for example LIMIT 300000,45 query takes about 6 second more then retrieving LIMIT 45,45

Which is the best and fastest way to paginate records?

thanks :)

My table looks so:

id (AUTO) | username | password | email

i used a simple query:

SELECT * FROM table ORDER_BY username ASC LIMIT 300000,45

2
phpsqlcodeigniterpagination
What does your table look like, what's the SQL you're using (including the ORDER BY clause to go along with the LIMIT), and what indexes have you defined? – Matt Gibson

2 Answers

3
votes

In the case LIMIT 45, 45 you are selecting 45 rows with an ofset of 45, whereas the LIMIT 45, 300000 limits 300000 rows from the offset 45. This may explain the speed issue.

3
votes

Unfortunately, because of how limit works, you're basically required to iterate through the result set and find the Nth item (in this case 300,000). It is an O(n) operation. Granted, it is done through a database, so it is a lot more efficient, but it is still O(n), and an offset of 1 million will still be slower than an offset of 1.

Now, you can speed things up by placing an index on username. My guess is that there isn't one at the moment and that would account for some of the discrepancy between the two times, but no matter what you still need to look at 300k records, so even if c is very small in O(C*n), it will still be O(n).