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