Efficient Paging for mysql stored procedures

We provide this mysql query page, which is fast and requires Paging for hundreds of millions of data records. Let's take a look at the detailed Writing of the mysql stored procedure.

We provide this mysql query page, which is fast and requires Paging for hundreds of millions of data records. Let's take a look at the detailed Writing of the mysql stored procedure.

Create procedure [dbo]. [PrGs_Nation_Task_GetList]
@ PageSize int = 100, -- the number of records displayed on each page. The default value is 100.
@ PageIndex int = 1, -- the current page number to be displayed is extracted. The default value is 1. The database returns a page number based on PageSize and PageIndex calculation.
@ RetTotal int output, -- total number of records
@ RetCount int output, -- number of returned records
@ RetPageIndex int output, -- output the current page number
@ ReturnDesc varchar (128) output -- return operation result description
As
Begin
Set nocount on
Set xact_abort on
Set @ RetTotal = 0
Set @ RetCount = 0
Set @ RetPageIndex = @ PageIndex

-- Multi-condition Value
Declare @ Err int -- Error
Declare @ PageCount int -- total number of pages
Declare @ BeginRID int -- start row Rid
Declare @ MaxRow int -- last line
Select @ RetTotal = count (*)
From NationTask
Select @ Err =@@ ERROR
If @ Err <> 0
Begin
Set @ ReturnDesc = 'failed to extract the total number of national tasks! '
Return-1
End
-- If no data exists, an empty result set is returned.
If @ RetTotal = 0
Begin
Set @ ReturnDesc = 'No country task record for the current condition! '
Return 1
End
-- Calculate the total number of pages
Set @ PageCount = @ RetTotal/@ PageSize
If @ RetTotal % @ PageSize> 0
Begin
Set @ PageCount = @ PageCount + 1
End
-- If the total number of pages is exceeded, an empty result set is returned.
If @ PageIndex> @ PageCount
Begin
Set @ ReturnDesc = 'No country task record for the current condition! '
Return 1
End
-- Get the Rid of the first record of the page to be returned
Set @ MaxRow = @ PageSize * (@ PageIndex-1) + 1
Set rowcount @ MaxRow
Select @ BeginRID = Rid
From NationTask
Order by Rid desc

-- Return data list
Set rowcount @ PageSize
Select Rid
, TaskName
, TaskTitle
, ImageID
, EffectID
, StartTime
From NationTask
Where Rid <= @ BeginRID
Order by Rid desc
Set @ RetCount = @ rowcount
-- End
Set @ ReturnDesc = 'the national task list has been extracted! '
Return 1
End