MySQL Forums
Forum List  »  Newbie

Efficient MySQL for pagination of large amounts of data on website
Posted by: Jack McGuire
Date: August 13, 2010 10:14AM

Hi there.

There is a very interesting article on how to use MS SQL Server to efficiently provide records for pagination on a web site at http://www.4guysfromrolla.com/webtech/042606-1.shtml#postadlink as written by Greg Hamilton. I'm wondering if this can be ported into MySQL

Here is the code from the website mentioned above somewhat paraphrased and commented by me...

# Create a stored procedure that takes 2 parameters
# startRowIndex which is the first row of data to appear on our page of
# paginated results
# maximumRows which is the total number of rows to display on our page of
# paginated results

CREATE PROCEDURE [dbo].[usp_PageResults_NAI]
(
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @startRow int

-- Get the first employeeID for our page of records
# SET ROWCOUNT is the equivalent of LIMIT in MySQL
# This SELECT statement is where the magic happens. The statement is only
# executed until startRowIndex records have been processed. But apparently
# MS SQL Server knows that @first_id is looking for only the last record
# so the employeeID of the last record is put into first_id and because of this
# it just wizzes on to this record. Here I believe is where it's all happening.
# MS SQL Server can simply get this last record very efficiently. But I can't
# figure out how to do an equivalent operation in MySQL. In effect what we
# need to do is something like
# SELECT employeeID from employees ORDER BY employeeID LIMIT @startRowIndex
# and then the result set we get we need to LIMIT to just 1 - the last record.
# Obviously using LIMIT @startRowIndex,1 negates the whole point of this exercise
# as we are just using inefficient offset/limit which is exactly what we are
# trying to avoid.

SET ROWCOUNT @startRowIndex
SELECT @first_id = employeeID FROM employees ORDER BY employeeid

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
# Again here the equivalent would be to set a LIMIT in MySQL

SET ROWCOUNT @maximumRows

# Now that we know the EmployeeID of the first record for our page of results
# we can simply SELECT WHERE EmployeeID >= @first_id and LIMIT the results
# to the maximumRows that we require.

SELECT e.*
FROM employees e
WHERE employeeid >= @first_id
ORDER BY e.EmployeeID

SET ROWCOUNT 0

GO

##########################################
If anyone has any ideas on how to implement this in MySQL please let me know. Otherwise any other suggestions for efficient extraction of records for use in a web pagination application would be most welcome.

Thanks for you help

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.