MySQL Data high paging efficiency

Source: Internet
Author: User
Tags insert mysql sort

My code is a large amount of data to improve paging efficiency test code
--Improve paging efficiency: to achieve paging only read display data, you need to create a database in the database "testforpaging"
Use testforpaging
Go
--Create TABLE Somedata
CREATE table Somedata
(
ID int primary KEY,
name varchar () NULL,
Description text
)
Go
--insert data
INSERT INTO somedata values (1, ' num1 ', ' 1th ')
Go
insert INTO somedata values (2, ' num2 ', ' 2nd ')
Go
Inserts into the Somedata values (3, ' num3 ', ' 3rd ')
Go
inserts into Somedata values (4, ' num4 ', ' 4th ')
Go
INSERT into Somedata values (5, ' NUM5 ', ' 5th ')
Go
--Total number of data 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 the data entry between the 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
--A stored procedure that enables you to view data entries between specified data levels
CREATE PROCEDURE Getdatapaged
(
@startRowIndex int,
@maximumRows int,
@sort varchar
)
As
--Ensure that the sort is specified
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



Related Article

Alibaba Cloud 10 Year Anniversary

With You, We are Shaping a Digital World, 2009-2019

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.