CODEWITHSUNDEEP
mysqlsqlpagination
Regual
Regual

Reputation: 377

pagination using mysql

I am doing pagination with sql server using this technique and its working well. how can i do with mysql

here is my sql code

OFFSET 1 ROWS FETCH NEXT 20 ROWS ONLY

but how about mysql?

Upvotes: 0

Views: 1880

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562951

Answers like MySQL Data - Best way to implement paging? will tell you this is the general form of the query:

SELECT ...
ORDER BY <expr>
LIMIT <count> OFFSET <skip>;

But be warned that MySQL really examines all the rows up to <skip> + <count>. So as you advance through your paginated dataset, the more costly it is.

A better strategy is to use the primary key:

SELECT ...
WHERE id > ?
ORDER BY id
LIMIT <count>;

This only examines <count> rows, starting with the first row after the specified value in your query parameter. This is much better for scalability when you paginate through large result sets, but of course you need to know the right id value to search for.

If you are viewing page N+1 after viewing page N, then you have the advantage that you can get the max id value from the result of viewing page N, and use that as the starting point for page N+1.

Upvotes: 2

Related Questions