CODEWITHSUNDEEP
phpsqlcodeigniterpagination
Filippo oretti
Filippo oretti

Reputation: 49817

Codeigniter pagination avoid LIMIT with OFFSET

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

Upvotes: 1

Views: 4076

Answers (2)

cwallenpoole
cwallenpoole

Reputation: 81998

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).

Upvotes: 3

Aif
Aif

Reputation: 11220

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.

Upvotes: 3

Related Questions