MySQL Forums :: Newbie :: Efficient MySQL for pagination of large amounts of data on website


Advanced Search

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


Subject Written By Posted
Efficient MySQL for pagination of large amounts of data on website Jack McGuire 08/13/2010 10:14AM
Re: Efficient MySQL for pagination of large amounts of data on website Barry Galbraith 08/13/2010 09:39PM
Re: Efficient MySQL for pagination of large amounts of data on website Jack McGuire 08/16/2010 04:07AM
Re: Efficient MySQL for pagination of large amounts of data on website Rick James 08/17/2010 07:28AM
Re: Efficient MySQL for pagination of large amounts of data on website Miloš Rašić 10/08/2010 07:55AM
Re: Efficient MySQL for pagination of large amounts of data on website Rick James 10/08/2010 08:34AM
Re: Efficient MySQL for pagination of large amounts of data on website Miloš Rašić 10/08/2010 10:34AM
Re: Efficient MySQL for pagination of large amounts of data on website seo somsax 10/09/2010 03:49PM


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.