How to improve data paging efficiency in MySQL

[Introduction] This code of mine is a test code to improve the efficiency of paging when there is a large amount of data--Improving the efficiency of paging: only reading the display data when implementing paging, you need to first create a database "TestForPaging" in the database use TestForPaginggo- -Create table SomeDatacreate table SomeData(id int

My code is a test code to improve the efficiency of paging when there is a large amount of data
--Improve paging efficiency: only read the display data when implementing paging, You need to first create the database "TestForPaging" in the database
use TestForPaging
go
--Create table SomeData
create table SomeData
(
id int primary key,
name varchar( 30) null,
description text
)
go
--Insert data
insert into SomeData values(1,'num1','Article 1')
go
insert into SomeData values(2,'num2','Article 2')
go
insert into SomeData values(3,'num3','Article 3')
go
insert into SomeData values(4,'num4','Article 4')
go
insert into SomeData values(5,'num5','Article 5')
go
--data Total number of entries
select count(*) from SomeData
go
--Add a data level to 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 for viewing data entries between specified data levels
create procedure GetDataPaged
(
@startRowIndex int,
@maximumRows int,
@sort varchar
)
AS
--Make sure to specify 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

The above is the detailed content of How to improve data paging efficiency in MySQL. For more information, please follow other related articles on the PHP Chinese website!