How to Improve the paging efficiency of MySQL Data

How to Improve the paging efficiency of MySQL Data

The data editing and inserting tutorial in this section briefly introduces how to improve the paging efficiency of MySQL Data. The following code is a test code that improves the paging efficiency when the data volume is large.

If you also need to set MySQL Data to improve paging efficiency, let's take a look at the code segment!

-- 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