MySQL Forums
Forum List  »  Falcon

Fastest Way to Do Row-Number Operations?
Posted by: Will Buckner
Date: April 11, 2007 01:52PM

I have a query which doesn't seem to scale too well. The performance isn't HORRIBLE (around 2 seconds on 20,000 rows), but speeding this up would help the performance of my application quite a bit. The goal here is to return starting and ending records for each "page" of records, with each page having 15 records. Converting this to a temporary table type query doesn't seem to help the performance any. Any ideas?

SET @rowcount=0;
SELECT
*
FROM
(
SELECT
@rowcount:=@rowcount+1 AS rc,
person.person_id AS personID,
person.last_name AS lastName,
person.first_name AS firstName,
person.city AS city,
person.state AS state
FROM
person
GROUP BY
personID
ORDER BY
lastName ASC
) AS tMiddle
WHERE
(tMiddle.rc-1) % 15=0
OR (tMiddle.rc) % 15=0
OR (tMiddle.rc) = 19261
ORDER BY
rc ASC;

Options: ReplyQuote


Subject
Written By
Posted
Fastest Way to Do Row-Number Operations?
April 11, 2007 01:52PM


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.