Improves paging efficiency when MySQL has a large data volume

My code is a test code that improves paging efficiency when there is a large amount of data.

-- Improve paging efficiency: To achieve pagination, only display data is read. You must first create the Database "TestForPaging" in the database"

Use TestForPaging

Go

-- Create SomeData table

Create table SomeData

(

Id int primary key,

Name varchar (30) null,

Description text

)

Go

-- Insert data

Insert into SomeData values (1, 'num1', '123 ')

Go

Insert into SomeData values (2, 'num2', '123 ')

Go

Insert into SomeData values (3, 'num3', '123 ')

Go

Insert into SomeData values (4, 'num4', '123 ')

Go

Insert into SomeData values (5, 'num5', '123 ')

Go

-- Total number of data entries

Select count (*) from SomeData

Go

-- Add a data level for each record

Select name, description, ROW_NUMBER () over (order by id desc) as dataLevel from SomeData

Go

-- View data entries between specified data levels

Select dataLevel, name, description from

(Select name, description, row_number () over (order by id desc) as dataLevel from SomeData)

As datawithleverl where dataLevel between 2 and 4

Go

-- Implements the stored procedure of viewing data entries between specified data levels

Create procedure GetDataPaged

(

@ StartRowIndex int,

@ MaximumRows int,

@ Sort varchar

)

AS

-- Make sure that the specified sort

If len (@ sort) = 0

Set @ sort = 'id'

-- Query with Parameters

Select dataLevel, name, description from

(Select name, description, row_number () over (order by @ sort desc) as dataLevel from SomeData) AS datawithleverl

WHERE dataLevel> (@ startRowIndex * 10) AND dataLevel <= (@ startRowIndex * 10 + @ maximumRows)

Go