CODEWITHSUNDEEP
mysqlsql
Osushi
Osushi

Reputation: 107

Good pagination performance for mysql

For example, my table has this data:

id(auto increment)     :place     :views
1                      :test1     :20
2                      :test2     :50
3                      :test3     :30

I'm thinking about pagination system.

When I sort views, I use this sql.

select * from table order by views desc;

Here is result:

id    :place    :views
2     :test2    :50
3     :test3    :30
1     :test1    :20

I'd like to use this data for pagination.

Page 1:
select * from table order by views desc limit 0, 2;
Page 2:
select * from table order by views desc limit 2, 2;

However, if this table has many data, the DB performance is lazy.

I found good slide for this problem. http://www.slideshare.net/Eweaver/efficient-pagination-using-mysql

But, my data doesn't have sortable id.

I'm searching any good idea.

Upvotes: 0

Views: 279

Answers (2)

spencer7593
spencer7593

Reputation: 108400

To implement efficent "next page" functionality, if id is UNIQUE in mytable:

CREATE INDEX mytable_IX1 ON mytable (views, id);

For the first page:

SELECT id, place, views 
  FROM mytable
 ORDER BY views DESC, id DESC
 LIMIT 2

Retrieve the rows. If you got the full two rows, there might be more rows, so keep the views and id values from that "last" row, for use in "next page" query.

For the next page, use the values of views and id that were saved from the "last row" from previous page:

SELECT id, place, views
  FROM mytable
 WHERE views <= :pp_views AND ( id < :pp_id OR views < :pp_views )
 ORDER BY views DESC, id DESC
 LIMIT 2

If you get the full two rows, then you can get a "next page", just again use views and id values from "last row" of previous page, query is exactly the same:

SELECT id, place, views
  FROM mytable
 WHERE views <= :pp_views AND ( id < :pp_id OR views < :pp_views )
 ORDER BY views DESC, id DESC
 LIMIT 2

It's not clear why this approach doesn't work for you. Do you not have a unique column on the table you can use? (Your question suggests you have a unique (auto_increment) id column.)

Upvotes: 1

exussum
exussum

Reputation: 18550

in your case i would index views and next be a link to last shown id in your example next would be &index=3

then the query would be getting the views id 3 has and then searching less than that with the limit.

Upvotes: 0

Related Questions