Paging using MySQL is implemented.
As a full scan measure, we avoid paging using Limit Offset and ensure performance with where and between clauses.
http://blog.yappo.jp/yappo/archives/000835.html
However, one question came out. As mentioned on the above site, measures such as the next page and the previous page can be paging while maintaining high performance using the LastID of the previous page record. Like Google's search results, I can't come up with the idea of a rich paging implementation like "Front 1 2 3 4 5 6" with high performance.
The current situation and the measures we want to implement are as follows.
■Examples of Spots table contents
spot_id | name | spot_category_id
1 | hoge | 1
2 | hugo | 2
3|huga|1
・・・・
100000 | hego | 1
First of all, if there is a table above, Get 10 values for spot_category_id
select * from spots where spot_category_id = 1 order by spot_id asc limit 10;
For example, 10 of the following values have been returned.
spot_id | name | spot_category_id
1 | hoge | 1
3|huga|1
・・・・
100|huge|1
100000 | hego | 1
If you want to get 10 of the next page with high performance, you can receive the LastID from the application and use it to issue the SQL below.
select* from spots where spot_category_id=1 and spot_id>100000 order by spot_id asc limit 10;
If you're going to do something like "Front 1 2 3 4 5 6" like Google's search results, the problem is that since spot_id is not numbered consecutively (sparallel), it's hard to have LastID in the URL parameters of each page, so you have to use Limit Offset, which scans DB full.
Here, somehow. Do you have any ideas for implementing measures such as "forward 1 2 3 4 5 6th" while maintaining high performance, such as Google's search results?
mysql
I've never dealt with such large-scale data (millions * hundreds of rows), but I've come up with about three things.
It depends on the actual table structure, but
select * from spots
inner join(
select spot_id from spots
where spot_category_id = 1
order by spot_id asc
limit100000,10
)s
onspots.spot_id=s.spot_id
It may be possible to make some improvements if you first set the heavy Limit Offset in the index column.In this case, the spot_category_id column should also have a non_unique index.
If you absolutely avoid Limit Offset, you might want to re-turn the spot_id column every time you update it, or add a new column to manage the order.(Valid if tooth loss does not occur often)
If the number of combinations of conditions is not so large even though the where clause covers all possible patterns, there is another way.(At most, management is complicated, not impossible.)
You can calculate and store spot_id corresponding to the paging location in a separate table or key value store DB (KVS).
The advantage of this is that even if spot_id has a tooth loss, you only need to subtract 1 for the data that holds the value after the tooth loss, so the update process doesn't take much time.(Some KVS products guarantee atomicity in terms of numerical increments/decrements.For example, Redis)
© 2024 OneMinuteCode. All rights reserved.